Tech Problem Aggregator

Migrating Alpha 4 database to Access, Memo Fields?

Q: Migrating Alpha 4 database to Access, Memo Fields?

In DOS-based Alpha 4, a database I've been using for years, text fields were limited to about 250 characters, but a memo field, stored separately from the rest of the record, if I understood this, was possible with far more space.  In migrating via Excel from Alpha 4 to Access, I seem to have lost the contents of the Memo field.  Does anyone know the filename where such fields were stored, perhaps dependent on the original table name to which they apply?  I do not think there were any such files with the .dbf extension, which constitute tables typical for databases.  I imported all files with that extension, and there were only 2, the 2nd a set of "child" records related to the first.  Any hints on where to look and how to re-attach this information would be appreciated.  I'm presuming some key field in common, of course, but don't know which of the many files in the old collection of such (that orchestrated all the database functions) is the one to try to import into, perhaps first, Excel, and then into Access.  Thanks for any help offered. (One thing I'll be trying, not done yet, is to just look for large files, regardless of extension, and snoop to see if I can tell what they contain.)

More replies
Answer Match 92.4%

Request any help possible,

- Involves MS Access 97 SR-1 and MS WORD 97 SR-1

-Report generated for records Containing many Memo-Type Fields.

Reports are fine when Printing from Access, However Using Publish It with MS WORD or Save As/Export... Commands prouduces an RTF file with many memo field values truncated (Records with larger amounts of Data)

There seems to be no set limits to number of Characters (256, 512, 1024)

I've tried using the free support Site (Knowledge Base) And have not found an Adequate Answer.

Thank You In Advance for Any Help.

blakbagger
 

A:Access report memo-fields truncated on export to RTF File

7 more replies
Answer Match 85.68%

I belong to a religious volunteer group and we have a database of about 10,000 names in Alpha 4 database. I want to convert it to Access and I was thinking of buying a program http://www.simx.com/simx/simx.stt/comp=cgs...ctr=H12_c5j885n It's called Text Converter and it costs $300. Does anyone know anything about this company? Are there any other programs out there that may be better? Thanks for your help. Sannunziata

A:Converting Alpha 4 Into Access Database

I know nothing about the Alpha4 Db, but it does seem that you can export data to Excel files at least. Have you posted about your problem to the Alpha Forums? One would think there would be a less expensive way to convert records to Access format.http://msgboard.alphasoftware.com/alphafor...&order=descRegards,John

2 more replies
Answer Match 81.9%

I've got a form in access and all I want to do is make a "total" box that adds 5 fields with numbers (not all those fields may be populated at the same time). So if the first field has a value and the second does not, my total field is blank. If both those fields are populated, it adds. What am I doing wrong? My syntax thru the expression builder is =[1st Account Balance]+[2nd Account Balance]

Thanks!
Linda
 

A:adding fields in access database

16 more replies
Answer Match 80.64%

Hello I have access 2013.

In a table I have fields Title FirstName LastName

Initially I created an addtional calculated field and used the following: (""+[Title]) & (" "+[FirstName]) & (" "+[LastName])

The above works fine.

In the future however I may want to transfer to sql server back end and I don't think sql will be happy with the calculated field in that format.

The second option I came up with was to create a query which would calclate the full name (etc...).

This again works fine using: FullName: (""+[Title]) & (" "+[FirstName]) & (" "+[LastName])

I am wondering whether once applied to a large number of records will the query run very slowly with having to concanenate a large number of records?

I would apprecaite any tips or help.

Thank you

Mark
 

More replies
Answer Match 78.54%

Retaining formatting in memo fields (reports)

I am being passed an MDB file from a back end system that uses SQL server to store information. The conversion of SQL-server to MDB seems to work well except when I encounter a memo or text field containing what I believe to be CR/LF characters.

Many of the fields in the database are Memo fields and hold 'verbal' responses that will comprise of up to 2000 characters.

The problem that i am having is that the memo fields seem to be substituting carriage return/linefeed for a strange square character and when I use the memo field in a report I lose all formatting of the input data.

The result is something along the lines of

Original
This is a list of data items

Item one
Item two
Item three

When I include the memo field in the report I get something along the lines of

This is a list of data items[][]Item one[]Item two[]Item three[]

The [] indicate the square I actually see in the text.

How can I convert the square character back to a CR/LF ?

Any help would be greatly appreciated.

Thanks in advance

Andy
 

More replies
Answer Match 77.7%

I have an older application that will be replaced. The old application uses foxpro tables. Data is stored in DBF files with memo fields in associated FPT files.

I'm trying to get the data into an excel file for import in the new application. The "regular" data in the DBF files doesn't pose a problem; but how can I access the memo fields in the FPT files and import them in excel ?
 

More replies
Answer Match 66.36%

Having an odd SQL problem attempting to get a database moved from one machine to another.  Both machines are running SQL 2008  32 bit and are both Windows 2008 servers.  When I attempt to back the database up using the SQL tools and restore the .BAK file to the new SQL server, I get the following error:Restore failed for Server ?servername? (Microsoft.SqlServer.Smo)Additional Information:System.Data.SqlClient.SqlError: RESTORE detected an error on page (44:2097184) in database ?RestoreDBName? as read from the backup set.Likewise when I attempt to simply copy the .MDF and .LDF files to the new server and attach them, I get the following error:Attach database failed for Server ?servername?Additional Information:An exception occurred while executing a Transact-SQL statement or batch.A system assertion check has failed.  Typically an assertion failure is caused by a software bug or data corruption.  To check for database corruption, consider running DBCC CHECKDB.  If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft.  An update might be a vailable from Microsoft in the latest Service Pack or in a QFE from Technical Support.Could not open new database ?NewDatabaseName?  Create DATABASE is aborted.Location: ?logmgr.cpp?:3277Expression: lfh->lfh_startOffset==startOffsetSPID:55Process ID: 292876 (Microsoft SQL Server, Error: 3624)From everything I?ve read, these errors generally i... Read more

More replies
Answer Match 66.36%

Hi all,

I?m really in need of some help from some Access experts! I?m a newbie to Access, but I got myself busy with quite a big project... I?m trying to map out the product supply to shops in the whole country. I?ve come quite far for a starter, but I keep having 3 questions, it would be great if you could help me with this!

I have, amongst other tables, two tables called Stock_counted and Reported_quantity.

The table Stock_counted has the following fields:
- Shop_number
- Product_code
- Quantity_counted

The table Reported_quantity has the following fields:
- Shop_number
- Product_code
- Quantity_in
- Quantity_lost_or_returned
- Quantity_out

1. What I would like to do is to make it one table based on Shop_number AND Product_code. So in one record I would like to have Shop_number, Product_code, Quantity_in, Quantity_lost_or_returned, Quantity_out and Quantity_counted. How do I do this, making sure that the quantities end up behind the right product and shop? It is possible that some shops don?t have all items, while other shops might not have all products reported, or both. On top of that, I have another table with a price for each product, which needs to be related to it as well.

2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?

3.... Read more

A:How to combine fields in Access 2010 based on 2 equal fields?

  
Quote: Originally Posted by Josephine87



2. It happens that some products are found more than once in one specific shop and therefore appear more than once in the table, with the same shop number and product code. How do I automatically SUM these quantities, so that this product only shows up once per shop?



select shop, product, sum(quantityfield1), sum(quantityfield2)
from sometable
group by shop, product

1 more replies
Answer Match 65.52%

i am creating a database app. i would like to create a dropdown list of all the available fields in a particular table for the use of filtering data (adoObject.recordset.filter). i know that i want to fill the dropdown list in the form load event but i'm not sure how to get or retreive the available fields to fill the list. i have been able to fill lists with info from any one field, but i would also like to fill a list with all the available fields as well. anyone have any suggestions for a beginner such as myself???
 

A:database fields in vb 6

You need to use something like this:

Private Sub Form_Load()
'Create the recordset
With MyRecordset
For intField = 0 To .Fields.Count - 1
Combo1.AddItem MyRecordset.Fields(intField).Name
Next
End With
End Sub

This uses the fields collection of the recordset to populate the combobox.
 

2 more replies
Answer Match 65.52%

Having an odd SQL problem attempting to get a database moved from one machine to another.  Both machines are running SQL 2008  32 bit and are both Windows 2008 servers.  When I attempt to back the database up using the SQL tools and restore the .BAK file to the new SQL server, I get the following error: Restore failed for Server “servername” (Microsoft.SqlServer.Smo) Additional Information: System.Data.SqlClient.SqlError: RESTORE detected an error on page (44:2097184) in database “RestoreDBName” as read from the backup set. Likewise when I attempt to simply copy the .MDF and .LDF files to the new server and attach them, I get the following error: Attach database failed for Server “servername” Additional Information: An exception occurred while executing a Transact-SQL statement or batch. A system assertion check has failed.  Typically an assertion failure is caused by a software bug or data corruption.  To check for database corruption, consider running DBCC CHECKDB.  If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft.  An update might be a vailable from Microsoft in the latest Service Pack or in a QFE from Technical Support. Could not open new database ‘NewDatabaseName”  Create DATABASE is aborted. Location: “logmgr.cpp”:3277 Expression: lfh->lfh_startOffset==startOffset SPID:55 Process ID: 292876 (Microsoft SQL Server, Error: 36... Read more

A:The problem with SQL after migrating the database to another machine

Are both servers running the same service pack (SP4) on SQL 2008?

1 more replies
Answer Match 63.42%

In an OpenOffice (version 3) text document it is possible to insert a table containing merge fields that are populated from a relational database.

It is also possible to perform calculations on cells, as in a spreadsheet. That is, a formula editor allows a cell to contain a formula that will display the result of a calculation on two other cells of the table.

This works perfectly with hard-coded data in the cells but I have been unable to find a way to perform calculations on cells containing merge fields representing data from an external source. All attempts to do so have produced an "Expression is faulty" message with no further hint. I have been unable to find anything relating to this in the somewhat chaotic OpenOffice documentation.

The aim is to convert a spreadsheet-based invoicing system to run from a relational database, thus eliminating duplicated data such as company names and addresses, and making integration with other documents using some of the same data easier and more efficient.

Can anyone help, please?
 

A:Calculating on fields extracted from database in OpenOffice

it may be the imported data from the data base is TEXT rather than a number to calc
in excel you can use =value() to convert a number thats formated as text into a number

I just tested in OpenOffice Calc and it has the same issue

format a cell as TEXT put a number in and it is right justified and will not calc
use =value() to convert to a number
now you can calc
 

3 more replies
Answer Match 63%

I'm using Crystal Reports XI.I have two tables with the same fields: current invoices and past invoices. I want to join the two so that I will be able to create a list of all current and past orders per customer and write formulas to total up various info like count each customer's orders and sum freight costs.I have tried creating a right outer join based on the customer number field, and the link is enforced on both tables. This results in many duplicate records from each table. Select Distinct Records is checked.I would love any suggestions about how to remedy this as well as how to write formulas based on the union of the two tables. I'm pretty new to Crystal but have not run into issues until trying to combine tables with the same fields, so I assume there is a way to set it up appropriately that I'm unaware of. Thanks much.EDIT: I found where to put an SQL statement to join the tables in a union instead, so I think I have solved this.

More replies
Answer Match 62.16%

Hiya

Moving Data from a Database into Microsoft Excel 97 Using Fields

System Requirements

Excel 97 or higher

Operating System - Windows 95 & 98, NT 4.0 & 2000, Windows Me, Win XP
http://www.microsoft.com/downloads/release.asp?ReleaseID=43948&area=search&ordinal=14

Regards

eddie
 

More replies
Answer Match 60.48%

I have an access main form that contains a subform. The subform is setup to display in datasheet view.

On the subform there is a memo type field. Is there a way to have the memo field to word wrap?
 

A:Solved: Access Subform - Memo Field

I don't think you can in Datasheet view. You could try setting the Can Grow property to yes.
 

2 more replies
Answer Match 60.06%

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!
 

A:Link Child Fields and Link Master Fields in MS Access 2003

climbingrose said:

Hi,

I got a form A and a subform B. I use B to display data retrieved from a table while use B to simutaneously update that data. More particularly, if I click a row in B, that row will be display on A and the user will be able to edit that data. The forms were designed by someone in MS Access 97. I converted it to MS Access 2003 format. Therefore, I have to link form A and form B. However, I couldn't find Link Master Fields and Link Child Fields in MS Access 2003!

Any ideas? Thanks in advance!Click to expand...

It really sounds like form A and subform B are both using the same table, and are not a Master/Child field link situation. But rather, form B (the subform) will have code in the "On Current" event that will find the current record on the form B within the form A, therefore allowing that record to be edited. Review your original post, I think you used the "B" one place where you meant to say "A". No matter what, you need to clear up with us out here if both form A and form B are using the same table.

Vic
 

1 more replies
Answer Match 59.64%

I am currently using Microsoft Access 2002. I wanted to know if there was a way for me to append an entire column from a table into a single memo field.

I am trying to make a distribution list memo field in an access table. I wanted to grab an "email address" column to upload into this memo field. I could reference the memo field later on when I send emails from Access.

Is there any way to do this?
 

A:Microsoft Access - Appending Table To Memo Field

First, you don't really give enough info: is this a one time append or is this for continuing functionality in a form?

If it is the latter, what you would probably need to to is create a list box that populates all the email addresses in your database. Then, based on which items in the box you chose, you could loop through, using vba, and create a string value. Once you have the string with the email addresses, you would commit that to your memo field.

Hope this helps,

MBN
 

2 more replies
Answer Match 59.64%

Hi,
Can someone explain how you can put tabs in a text that you are putting in a memo field? I would like to align the text in different columns so it appears that way in a merged document.
Help is much appreciated,
Filip
 

A:Access 2010: putting tabs in memo field

16 more replies
Answer Match 59.22%

And yes, I did set the property to 'can grow' and 'can shrink'. I can't figure what the problem is. Even if I stretch the field out huge, it still is cut off when I view the report. And in one case, the same field prints fully when it is used on one report but on another similar report the same field is cut off. I can't see any differences between the two report settings.
 

A:Access 2007 Memo field not fully showing on report

8 more replies
Answer Match 58.38%

MS Access:
I have several fields in a form that uses two tables. I would like to set up a command button so that when you are on a current record, it will copy the field information from the record you are on to other fields in the same record. I do not know the SQL commands to set this up.

For example, there are name fields, address fields, and phone to be copied. Reason: One is contact information; one is survey information. In most cases the information is the same but on rare instances, the information needs to be entered (when different).
This is not an update for all records in the database. This is based individually on each record.
Not sure if you need to do On current, on click, or after update.
Not sure if default value in form can be used.
Please help.
 

A:Copy data fields in same table to other fields in same table MS Access

16 more replies
Answer Match 57.96%

Hi,
I don't support Access, nor have I been trained in it, but I've been asked if I can help in this problem.
An Access database connects externally to a SQLServer database at login time for the application. How do I find which external database they are connecting to? I know that there is a way to go behind the forms to see the source code, but I cant remember how to do this.
I cannot find an obvious User/System file dsn on the PC to give me a clue.
Thanks in advance
 

A:Accessing external database from Access database

9 more replies
Answer Match 57.96%

Here's the question- how do I open a form in an existing database from another db in Access 2010 using VBA and give it focus. I can open the external db and it's form but cannot figure how to give it focus. I'm trying to create a master form that will call various databases that each have a form. Seems simple, open the master menu, click on a button that then opens the called db and the associated form. Do my business exit the db and am back at the master form. I would be okay to just close the master db, all it has it the main menu or leave it open. Doesn't matter which I just want to open the called db and form and have its focus.
 

More replies
Answer Match 57.96%

Hi Guys,

Im trying to write some code that will copy my access database , tables and data to a new sql server database, ive got the code to create the sql database but i need to populate it from my application

Any answers would be grateful

Thanks
Mark
 

A:Copy an access database to SQL server database using VB.NET

Google: import access to sql server
 

1 more replies
Answer Match 57.96%

I'm building a database application in Visual Basic .NET and I want to know what is the equivalent code of this when I want to make a connection to mysql:

Protected connString As String = _
"Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = " & loc & "\dbProject.mdb;"

Private cn As OleDb.OleDbConnection = New OleDb.OleDbConnection(connString)

Private ConnectionString As String = connString
 

A:Exporting MS Access Database to MySQL Database

You would have to have the MySQL driver for it on the server regardless.
 

1 more replies
Answer Match 56.7%

Hi all,
Can someone help me with this issue in the Crystal Report

I am using Crystal Report 2011 and Oracle database.
My query is to output some columns in 2 different table(A,B).
After run query, there is 126,978 rows in the result.
In table A, there are 36 different types. The result shows same
in the Oracle Sql Development and Crystal Report (126,978 rows).
it also shows 36 different types in the details section. But when I right click
on that column, there is 23 different types and missing other 13 types.
I tried many ways but without successful (still missing).
Is there any problem with the software? Data missing hence I cannot create correct
dynamic parameter. If it does not show all 36 different type, then my query possibly
wrong, but it can show in the details section, then browse data option should show all 36 types right?
Do you have any idea to solve this?

Thanks in advance.
 

More replies
Answer Match 55.86%

i have been using application(Visual Basic 6) in my office which uses SQL Server 7.0,now i wants to install windows 7 in my pc,i would like to know whether sql server 7.0 database can be restored in sql server 2008.pls guide me how to do the same

More replies
Answer Match 55.44%

I have MS Access 2000. I initially created a long book list several years ago. I recently found a template from the MS database for books that I downloaded that I like better. I have tried importing the data from the old database to the new one but with no luck. Both are in Access 2000. I am running Win2K.
Thanks
 

A:Transfer from Access Database to Access Database

6 more replies
Answer Match 55.02%

Something some of you guys might find useful.

Latest Changes -




Quote:
Now requires (and uses!) .NET Framework 3.5 SP1
- Significant improvements to the installer. Prerequisites are now handled in a much more user-friendly fashion (it's no longer "go to the Microsoft website and decipher geek talk and download stuff")
- The auto-updater can now download in the background, and then install the update after you've exited Paint.NET. Compare this to v3.36 and earlier that jump in your face and require the download and installation to happen right now, and block you from us
- New effect: Blurs -> Surface Blur, by Ed Harvey
- New effect: Distort -> Dents, by Ed Harvey
- New effect: Distort -> Crystalize, by Ed Harvey
... Read more

A:Paint.NET (Alpha) 3.50.3424.34110 Alpha

Ya beat me to it lol....

I get the FileForum RSS feed as a live BM in Fx.....

I'd spend way too much time if I listed even half the software I DL from them on a daily basis....

4 more replies
Answer Match 52.92%

Please help.

I work in a small office and we deal with credit card transactions by telephone. Currently, when a customer calls, we record their details on an A5 paper slip which is also fed into a MS Access 97 database. However, to improve accuracy and speed, I have created a new database.

I have 2 tables, no querys and 2 forms.

Table 1: View Credit Card Details
Table 2: Add Transaction

Form 1: View Credit Card Details
Form 2: Add Transactions

When we search for a customer's details by searching via their postcode within the V.C.C.D. form, we are presented with the customer's credit card no, address, tel no etc. My question is this: We would like to add a new transaction (bank authorisation code) without having to re-type all the details and also to prevent us displaying 2 forms. The (bank authorisation code) data would be going to the add transactions table anyway, it's just to prevent having to tab to another window.
Also, when we add that record, would it mean that every time we search for that postcode, would we presented with all the bank authorisation codes first before we could add a new record with same address details etc?

Also, I am using XP and MS Access 2002.

Any help appreciated.

 

A:Access 2002 (Please Use This) Migrating forms

One question: does each transaction have a bank auth code? If this is the only additional information that needs to be added, you could simply add the field into your table, then add it to the form.

I guess I am not entirely clear on the process you use. If you are able to enter the bank auth code at the time you create the Add Transaction record, then it's no problem, the above will work fine.

It's when you bring up the searching that you are going into a part of a process you haven't described before.

What appears when you search on postcode now? I guess I'm assuming the person could already have multiple transactions before the issue of bank auth even comes up....

Bottom line, I guess, is does each transaction have its own bank auth? If so, then just add it to your transaction form? If not, could you provide more details?

MBN
 

2 more replies
Answer Match 52.92%

Please help.

I work in a small office and we deal with credit card transactions by telephone. Currently, when a customer calls, we record their details on an A5 paper slip which is also fed into a MS Access 97 database. However, to improve accuracy and speed, I have created a new database.

I have 2 tables, no querys and 2 forms.

Table 1: View Credit Card Details
Table 2: Add Transaction

Form 1: View Credit Card Details
Form 2: Add Transactions

When we search for a customer's details by searching via their postcode within the V.C.C.D. form, we are presented with the customer's credit card no, address, tel no etc. My question is this: We would like to add a new transaction (bank authorisation code) without having to re-type all the details and also to prevent us displaying 2 forms. The (bank authorisation code) data would be going to the add transactions table anyway, it's just to prevent having to tab to another window.
Also, when we add that record, would it mean that every time we search for that postcode, would we presented with all the bank authorisation codes first before we could add a new record with same address details etc?

I have created a snapshot of the database to give, hopefully to give you a better understanding. Also, I am using XP and MS Access 2002.

Any help appreciated.

 

More replies
Answer Match 52.92%

I am exhausted by using windows-7 operating system.

we have tools developed in windows-xp environment. Now we have the plan to migrate all the tools to windows-07 OS.

After installing the tools in win-07 machine i am be not able to run the tool because of "Access violation error (with some error number like 0x7722E39EC, this number continuously changes each time i run the tool )" .

I have all the rights for the system and system is connected to the office LAN.

How to come out from this pitfall ?

Thanks in advance.

A:Access violation Error, Migrating from xp to win-07.

  
Quote: Originally Posted by Manu MJ


I am exhausted by using windows-7 operating system.

we have tools developed in windows-xp environment. Now we have the plan to migrate all the tools to windows-07 OS.

After installing the tools in win-07 machine i am be not able to run the tool because of "Access violation error (with some error number like 0x7722E39EC, this number continuously changes each time i run the tool )" .

I have all the rights for the system and system is connected to the office LAN.

How to come out from this pitfall ?

Thanks in advance.


Install those tools in Compatibility Mode

Be aware that obsolete software might not work with Windows 7

If Compatibility Mode doesn't work try XP-Mode
Windows XP Mode - Install and Setup

1 more replies
Answer Match 52.92%

I know that when using the standard Access wizards your Forms(.RecordSource) and your Combo Boxes(.RowSource) end up constantly connected to your data. That is bad practice in any multi-user environment. So my first question is how do you fix that.

Forms are easy enough donít use .RecordSource and donít use bound text boxes, instead have the Form make an ADODB request during Form_Load and then fill in those text boxes as unbound text boxes instead, RIGHT?

What about the Combo Boxes? Instead of using a SQL statement in your .RowSource, shouldnít you use a query? And then in that query properties set the Recordset Type to Snapshot? Doesnít that completely disconnect your form from the data? Am I thinking correctly here? And isnít this also moving your application closer to a VB solution?
 

A:Make Access program run better vs. migrating to SQL

I'd really think about moving to SQL Server, even if you migrate to one of the Express editions. A couple of questions you need to answer for yourself:

1) How big is/are my existing database(s)?
2) How many users are currently using your application
3) Are my databas(s) going to grow quickly in the future?
4) Are you adding more users on a regular basis?

One of the things I hated about access, in a network environment, is that, unless you select only a single row or very few rows, your entire db gets copied over the network. With a small number of rows, probably no big deal. But as more and more users add more and more data, it DOES become a big deal.

And with VB/SQL you can use a disconnected dataset. COnnect to it when you need to, otherwise disconnect from the source.

OR, IF you need to stay with your access tables/queries/etc., at least build your front end forms in vb. VB.net is so much better at moving data over the network (due to it's discconected nature, altho you CAN persist the connection).

Just my few cents worth.
 

1 more replies
Answer Match 52.92%

how many total fields can you have in an access table
I'm using access 2000
 

A:access fields

I believe the limit is 255, but I am not 100% sure. Even so, you will most likely never have that many fields in one table...
 

1 more replies
Answer Match 52.5%

I have a form with Qty, Price, Total, MTD(MonthToDate), YTD(YearToDate). I am only storing Qty and Price in the Table. I can get a sum for Total, but how do I get a sum for MTD and YTD?

Should I have a field in my table for the Total, MTD, and YTD?

What is the best way to set this up?
 

A:Summing fields in Access

10 more replies
Answer Match 52.5%

Hi,

I am currently building a shop type database...

I have made a 'transaction form' On the form is things such as transaction ID, customer details, staff details etc, then there is a subform, where the products on the transaction get entered into.

On the subform (Transaction/Product link Subform), there are the fields (among others...) 'Product ID', 'Price' and 'Quantity', I have then made an unbound field to calculate the subtotal which has '=[QuantityOrdered]*[p_Price]'

On the main form, I then wanted to have a 'total' field, whereby it adds up all of the subtotals from the subform, the problem is is that through the various methods i have used i keep on getting #Error or it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.

Any solutions? please

A:Calculated fields in Access

... it will only show me the subtotal of one record, when I need it to show me the total subtotals from all records.If I understand correctly what you said, I think you need to make a calculated field on the form which will say something like=Sum(the name of the calculated field on the subform)and put that total field in the footer of the form. The footer should contain form totals.

4 more replies
Answer Match 52.5%

I have two tables tEp and tLn.

I have a field in tLn for tEp but I need to be able to select multiple values, Ive followed guys on office and here: http://www.databasedev.co.uk/multivalued-fields.html

But I cant get the Allow Multiple Fields checkbox!! It just doesnt show!?

Why?
 

A:Access Multiple Value Fields

Worked out this only in accdb, is it possible to do something similar in mdb?
 

1 more replies
Answer Match 52.5%

Ok, i have a form that has a calendar...when you click on the "hiredate" or "birthdate" the calendar appears, and when you click anywhere on the detail area, it disappears...however, whenyou change a date on the calendar you must click in either the "hiredate" or the "birthdate" for it to take the change....i would like to make this automatic, so that when a person clicks the calendar the coresponding field...hiredate or birthdate, is changed....is this done with the "on update" event? any help would greatly help my grade!
if so could someone give me an example?

-Craig
 

A:Access: One Calendar for Two Fields?

Yes, well, a couple of questions, so that our solutions change your environment the minimum amount possible:

1. Are you sure that the calendar that appears is on the same form? Or is a special calendar form appearing and disappearing? You might want to poke around in the db window, and/or post back the details (macro or code behind the events, for example, on click) for hiredate and birthdate.

2. Also, what's behind the calendar? The tricky part of what you're talking about is having the same control (or form containing a control) for two different text boxes; that's why you're having to click in the appropriate box, I'm guessing: to tell the calendar which box to put its value in.

I'm guessing that you didn't build this initially? If these questions seem to complicated, just zip up an example version of the related table(s) and the form(s) in an .mdb file and send them to me. (Must be under 1MB.) But if you can post back the details on what's going on behind your controls, we can probably give you a faster response.
 

3 more replies
Answer Match 52.5%

I have this table that I want to have a drop down list for the builder and then the next field another drop down list that only lists those tracts that the chosen builder has. I am trying to create a form right from the table
 

A:Access lookup fields

Please read this previous post
http://forums.techguy.org/business-applications/485383-solved-access-lookup-based-previous.html
 

1 more replies
Answer Match 52.5%

I know how to concatenate field values in a record but I'm wondering if it is possible to concatenate field values in separate records. My reason is that I have a form showing data from a query made from two tables. I want only one record in the underlying data and if I join two tables where one has multiple matches then there will be multiple records in the query. Table 1 has a record with joining field f1 (no duplicates allowed) and Table 2 has multiple records for f1 with different f2 values. Here's what I mean:
T1 has f1 = 90000
T2 has
f1 = 90000, f2 = T
f1 = 90000, f2 = Y
f1 = 90000, f2 = N

Is there a way to set up a query operating on T2 to give a table with the value
f1 = 90000, f2 = Y,T,N

If so, then a join on f1 with this table would yield only one record for the form I display. As a fallback position should I just do a summary query on T2 and show the resultant records in a small subform?

Thanks in advance for any advice on this?
 

A:MS Access Concatenate Fields

6 more replies
Answer Match 52.5%

I am building a query in Access 2007 and when I select and drag a field from a query into the lower section of the building query screen it only brings the one field (like I want), but when I run the select query I am getting all the fields in the original query. How can I get it to only show the one field?
 

A:Access Query too many fields

Ensure that the query does not contain a column with the heading tablename.* or queryname.* where tablename and querynames are the names of your query table or query.
The "*" means all fields.
 

2 more replies
Answer Match 52.5%

Here's the situation:

I have been given several access databases with contact information in them. The contact information has all the usual info (address, phone, etc.); however, the name format is two different fields: a first name, and a last name. My goal is to import all the database information into ACT!

I was planning on doing this by exporting a text file with comma seperated values; the only problem, though, is that the existing ACT! database doesn't have two name fields, and instead only has one "Contact" field.

Sooo.. my question is, would it be best to try and convert the names to one full name in access, or is there some way to import and convert them with ACT!? And, whichever way is best, how exactly would I go about doing that?

Thank you very much!!
 

A:Merging two fields in Access and/or ACT

7 more replies
Answer Match 52.5%

In Access 97 SR-1 is it possible to create a lookup field in a table that is also a required field? I have created a table in Access containing several lookup fields. Now the user is requesting that the fields also be required - that the person doing the input via a form would not be able to advance to the next record before choosing an option in each lookup field in the current record. Setting the required switch to "yes" after the field has been created as a lookup field does not prevent the user from advancing to the next record in the form.

Please advise any suggestions. Thanks!
 

A:Access 97 required fields

You can set the "lookup" field back to a standard field and use a Combo box to do the look up for you.
 

2 more replies
Answer Match 52.5%

I must be having a brain fade here but what i want to do is real simple, just can't get it right.

I have a field on an MS Access form and all I want to do is check if something has been entered into it.

Something along thw line of :-
if field is blank do then
do something
else if field is equal to spaces then
do something different
end if

of the life of me I can't get it to work :-(
 

A:Blank fields in Access

I'm presuming this is just pseudocode for a corresponding Vb project.

If so then i recommend:

Option Explicit

Dim wksp As Workspace
Dim db As Database

Private Sub command1_Click()

Dim entry As Recordset
Dim search_string as string

search_string = txt_search_string.text

Set wksp = DBEngine.Workspaces(0)
Set db = wksp.OpenDatabase(App.Path & "\DATABASE NAME.mdb")

Set entry = dbfacilities.OpenRecordset("SELECT * FROM [TABLE NAME] WHERE [FIELD NAME] = '" & search_string & "'")

If entry.RecordCount = 0 Then
MsgBox ("The field is blank")

txt_search_string.Text = ""
txt_search_string.SetFocus

Else
'unsure of what you wanted here...
End If

Hope this helps.
 

3 more replies
Answer Match 52.08%

I am working for a company that has four people (four different computers) entering data into four different Excel 2010 files on a shared drive. I would like to create a master file on my own computer for the purpose of generating reports. How do I create one Access database that includes all of the information from the four different excel files? Also, can this Access file be continuously updated as the four Excel files are updated each day? Thank you!!!
 

A:Access 2010 Help Merging 4 Excel Files into One Access Database

9 more replies
Answer Match 51.66%

I produce my departmental purchase plans on MS Access so that I can attach my own symbols eg prioritising and run queries. I have been told that I can transfer/link/export individual fields from Access into a template order form set up in MS Word. Is this possible? How is it done? Is there an easier way?
Any suggestions?
John.
 

A:Linking Access Fields to Word

Hey there.

Is there some reason a mailmerge from Word won't work?

www.thewordexpert.com/word.htm

Check out how to set up a mail merge.
 

1 more replies
Answer Match 51.66%

Aloha FRIDAY Tech ppl!!
I've set fields in MS ACCESS 2010 table to be required fields --- BUT it "kicks" me out the form IF i try to save the record to test the field by leaving the field blank!!

Were should I enter the parameter to ask me to enter the required field then SAVE the record ...
thank you!!!!!
 

A:MS ACCESS table required fields

The table is the correct place to do this.
I am not sure what you mean when you say "save the record" as Acces automatically saves the record.
What happens if you try to close the form yourself, or move to another or new record?
 

3 more replies
Answer Match 51.66%

I have to concatenate the following by item number: I have attached a file with a sample table.

item number / memo subject / memo text
15056 / x/ e
15056 / t / r
15056 / s / q

there is also the issue where I have ~;~ in the memo text field. This is coming from some type of code in our ERP software. I would like to have this removed and not added in the final result which would be something like this:

15056/ x t s / e r q
I do not need comma's in between the data but if the field is blank i don't need that either.

I have researched and I have tried to do some VBA with Modules but I really don't know what I am doing.... Help!
 

A:Solved: Access - Concatenate fields

16 more replies
Answer Match 51.66%

Hello,
I am fairly new to database and I don't know much about VBA coding.
I am creating a database and I have created a form for end-users to submit create a new record. but I want all the fields except one to be required.
I tried to make them required in the table but it gives me error when I enter data through the form. Can anyone please help me with this.

I have the following fields to be required

First name
Last name
Date of birth
Department
Employee level
employee status
Employee ID
GID
Hire date
E-mail
Phone number
Model ID

Thank you in advance
 

A:Access 2010 Require Fields

6 more replies
Answer Match 51.66%

Hi

I have developed a simple database and used the built-in 'access search' facility that is applicable to all forms. There are around 7 fields in each table.

Problem: When I select more than one field (through the dropdown list), it lists all data instead of listing only data related to the first selected field.

Question: How do I link the fields so that during a 'search', if I click the first field and select a value (e.g. field size) then if I select the next field (e.g. colour) - it will only list colours pertaining to the selected field size - not all the colours of all the field sizes in the database table?

Any assistance appreciated.

Many thanks

Usman
UK
 

A:Access 2003 - linking fields

Hi

I requested the moderators to move this thread to the Busines Applications section. You will get more attention there, and most probably, the quickest solution as well.
 

2 more replies
Answer Match 51.66%

Hi,
Here is my problem. I have a table with details of enrolled students. Many of the students have the same name with only an initial or middle name to differentiate them.
I have a field each for forename, initial/middle name and surname in the table.
I need each field to be able to accept duplicate entries but where both forename and surname are identical, the initial/middle name must be a required field.
Problem is, not everybody has a middle name, so I cannot set the field property to required. I need it to be required ONLY in the case that the forename and surname are identical.
Anybody got any ideas?
I tried using ID, forename, initial/middle name, surname as PKs but that doesn't work.
Please explain in simple words, I am not a technical expert, just an admin.
Regards
Nick
 

A:Access 2010 unique fields

8 more replies
Answer Match 51.66%

I've looked for this and people don't seem to have the problem. I have a form that populates a single database. on enter a new refreshed form is presented to the user - but now my user has requested that 3 fields on the form not clear. She wants to be able update them if needed, but generally they will not be changed during a batch of input, after she has entered them the first time. How do I STOP them clearing?
 

A:Access form clears all fields

6 more replies
Answer Match 51.66%

I have an Access database where the data is being input in Access and displayed by Lotus Notes. Originally I had a number of Yes/No fields e.g. "Outside Storage" in the table. The person doing the Notes interface requested that I change these to Text fields which I did. The table values remained as Yes or No when I did this. The input forms kept their check boxes when I did this but as one enters data via the checkboxes the values are 0 and -1. If I delete the field from the form and add it again it comes as a text box which then displays Yes or -1, etc depending what is in the table.
The Notes person says data tables should not contain Yes/No fields or Combo Boxes like solid/liquid. My question is what is the best way to handle this assuming the Notes developer wants text fields in the table? Should I delete all the checkboxes from the forms and replace with text boxes showing Yes or No? Right now I have the validation of the table set to accept yes, no, 0 or -1. Finally, is the field on the form able to be changed back to a checkbox without changing the table field back to a Yes/No field? It seems as if the type of field placed on the form is inherited from the table.
 

A:Solved: MS Access Yes/No or Text Fields?

7 more replies
Answer Match 51.66%

I have a form with many subforms. The main form and subforms are linked by the access generated ID numbers. However, whenever I try to input data into one of the subforms I get the message, "The LinkMasterFields property setting has produced this error: Object or class does not support the set of events" I am not sure what that means, but it hasn't affected the form that I know of. The data still saves within the subforms. Anyone know how to remedy this problem?
 

A:Access Link Master Fields help!!

7 more replies
Answer Match 51.66%

Hi, I receive an excel file everyday that I need to import. However, the column header of the fourth column of data changes daily (it's the corresponding day's date). I would like to import/append the data to the previous day's import. Is it possible to import and append fields by their position number, e,g, the fourth column is not called using its column header but is called by its position, say "field4"(?).
 

A:Access: querying fields generically

PMCD, welcome to the Forum.
I don't think what you want to do can be done with just one Import query because of the name change and Append Queries use the field names.
You can Import the Data to a temporary table, change the name, append the data to the master table and the run a delete query to remove the data from the temp table.
What you could have is an SQL statement renaming the field, although I am not sure if SQL can handle the field name change, and then run the append query.
You can definitely use VBA code to change the name and then run the Append query though.
Or you change the field name manually prior to running the Append query
 

1 more replies
Answer Match 51.66%

I have a database, and I already have several fields "locked" so that chances cannot be made to these particular fields.

I have some command buttons that I currently have enabled, but would like to disable them when a certain checkbox has a "true" value (is checked).

How can this be done (if at all) on a "per button" basis, as I have some other buttons that I want to leave enabled all the time.

Thanks in advance!
 

A:Solved: Locking fields in Access

9 more replies
Answer Match 51.66%

I have generated an access report that shows the process comments for the different locations we have in the organization. What I need to do is to count the total number of comments we have per day for the daily report I generated. I used a textbox under the gourp footer with the expresson "=Count([Comment])". It worked fine to get the count of teh comments under each goroup. I need to gererate a grand total for the report summing the values of the textbox with the above expression. Also, is there a way to count the text fields one time as a grand total for the report. Thanks in advance.
 

A:Summing calculated fields in access

The unbound text box that you have used for the "=Count([Comment])" should have a name like text2, so you use the following in a another unbound text box in the report footer using the correct name for your counting text box

=sum([text2])

I am not sure about your last question "is there a way to count the text fields one time as a grand total for the report"
 

2 more replies
Answer Match 51.66%

I have a table in an Access database. This table has three columns in it, and I want to add a fourth column. I want the value of this fourth column to be "Primary" for every record.

What's the easiest way to accomplish this? I'm an Access newbie.

-- Richard
 

A:Auto-fill access fields?

6 more replies
Answer Match 51.66%

In an existing database, there are fields that have text I would like to separate (like first name and last name both in same field). Can this be done and how?

Thanks for your help!
 

A:Access - Can you separate text in fields?

Yes. You should probably start by reading up in Access help on the following functions:
InStr: Looks for a specific character, and returns its position in the string (data in text fields is called string data
Left([FldNm], x): Returns the leftmost x characters from a string
Right([FldNm], x): Returns the rightmost x characters from a string
Mid: Used to return part of a string, beginning from a midpoint, continuing a certain number of characters or to the string's end.

Access Help will explain to you how each one works in more detail. Basically, you test a field for a space using InStr; InStr will return 0 if it doesn't find one, or a position number if it does. If my name is "Joe Kool", for example, InStr will return 4. You then use Left() and Right() or Mid() to separate out the strings.

Let us know what more we can explain.
 

1 more replies
Answer Match 51.66%

I cannot change entry in ID field of Facilities Table due to 1-1 relationship to Test Data Current Table. I just want to modify it from "70100" to "AI-70100".
 

A:Modifying Fields in Access 2010

Is there just the one record that you want to change?
 

3 more replies
Answer Match 51.66%

Please help. I am trying to calculate a percentage of my yes's. I have several yes/no fields in which I'll need to calculate a rate. I would prefer to create a report that will calculate this for me. However, right now, I am really just looking for the easiest/quickest way to do this. So if I need to run a query first and base the report off the query, I'll do it. Please help ASAP if you can.

Thanks,
Tonya
 

A:Solved: Calculating Yes/No Fields in Access

16 more replies
Answer Match 51.66%

Hi there,

Let my preface this post by saying that I have some experience working with Access, but nothing official and so what I know is as a result of hacking my way through. Also, let me start by saying that I apologise if this seems long winded, but I am unsure how much info to give.

I've inherited a database at work which I need to develop further, but it uses some methods I am unfamiliar with, though I think I understand enough to know they need to be there.

Basically the database is a customer transaction system and its that particular section I am trying to sort.

I have a table (tblPupilTransaction) which is meant to be updated whenever a transaction occurs. It has :

Transaction Date
PupilID
EAN (item code)
Billing Status
Quantity
ValueExcVAT
ValueIncVAT

However, because there will only be a static group of customers that can purchase from us, we have a lookup table (tlkpPupil) which has :

PupilID
FamilyName
GivenName
CurrentFlag

My difficulty comes when developing the form that would be used to input the transactions. I had thought to use combo boxes to link FamilyName, GivenName and PupilID into one selection, but that doesn't automatically complete the exisiting PupilID field (which cannot have a null).

Likewise, when I use a combo box to put in the product data, the price is not automatically complete (these also have the appropriate lookup tables).

So, in short, I want to know how I create a form which will input into tblPupilTransaction w... Read more

A:Bound Fields on Access Forms

You are on the right track. Have the data in the Combo boxes. All it takes then is some simple VBA code in the Combos After Update Event Procedure to pass the values to the fields.
However, the only thing that should be stored for the Pupil and Product is the ID numbers.
The rest should only be "Displayed".
 

3 more replies
Answer Match 51.66%

Hoping this is one of those easy to answer, dumb questions.

I'm using Access 2002. I've created a form with subforms, and linked the data collected on the subforms to the main form via the master/child links.

What I now want to do is:

1. On the subforms, enable the user to select a value from a drop-down list, and then populate the remaining fields on the subform with data based on the user's selection;

2. Enable the user to edit the autopopulated fields, yet maintain the integrity of the original values so that they can be used again and again.

I'm a stumped as to how to accomplish this. As you guessed Access isn't exactly my forte, but I'm a quick learner.

Any ideas? Thanks for your time.
 

A:Autopopulating editable fields in Access

Hi Shug02,

Try this:

In the combo, add the extra data you want to use to autopopulate the form as extra columns.

You can do this by clicking on the Row Source in the combo box's property sheet. Increase the number of columns in the combo by setting the column count to the required number, and set the width of the additional columns to 0.

Now the combo will invisably return the associated data along with the original data... but this will be cunningly hidden from the user.

In the AfterUpdate event add an Event Procedure along the lines of:

Private Sub ProjectName_AfterUpdate()
Me![ProjectManager] = Me![ProjectName].Column(1)
End Sub

This will put the data in the first column of the ProjectName combo into the ProjectManager control on the form. If the column has a width of 0 it will be hidden from the user.
 

3 more replies
Answer Match 51.66%

have two tables like

Staff Table
............
Staff ID
Staff Name
Age
...and more

Staff Salary Table
..................
Staff ID
Staff Name
basic Salary
...and more

Now I have created the Staff Salary form in which I got the staff ID from the staff table using the lookup wizard query in the Staff ID field of the Staff Salary Table
SELECT [Staff Table].[Staff ID] FROM [Staff Table];

but I need one more

When The staff ID was selected from the drop down list, I need the relevant staff name (from the Staff table) also be displayed in the staff salary from .

Can you please let me know how to do this..
 

A:Getting the data from different fields [ MS Access problem ]

12 more replies
Answer Match 51.66%

I have a fully functioning dbase with the last Calculation that I cannot work out.

I am attaching a sample Dbase to help w the explanation.

I would like the Bonus Total to reflect all Totals. After totaling rate and standby total, I then want to choose my services and total out the Services total and refresh the Bonus Total with all of the updated info. I have stared merciless at this and tried several ways to make this happen.

I am slowly going mad. This is the last calculation then I am finished with this but have gone blank.

If anyone can help me, I would appreciate it.

As you tab through the dbase you will see that the Base rate is divided by 2 to get the standby rate and this reflects automatically in the Bonus Total in the top half of the dbase. Then you progress to service selections and the total is reflected upon pressing enter in the Services Total. This Total is what I want added to the existing Bonus Total that is calculated in the top of the dbase.

Sorry if I am repeating myself.

Anyone?
 

A:Solved: Access, Calculated Fields, OBP?? Anyone?

Bonus total has the calculation =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] as it's control source.
Once you do that you can no longer change the value using VBA.
So the answer is to replace the =[cboRateTotal]+[cboStbyRateTotal]+[cboServicesTotal] with the VBA version.
So either set the control source to nothing or make it a field in the table.
 

2 more replies
Answer Match 51.66%

Hi all,

I am making a report in Access 2003. I have salaries that will be displayed by the month. However, since we are only in April, the months May-December are not in the query that the report is based on. Is there a way that I can make the report display a 0 in the place of the months that aren't yet there? The months that are there will display the salaries.

I appreciate any help! Thank you!
 

A:Access 2003 non-existing fields

Are you using a Crosstab query for the Report?
If so you can force the Headings to be entered by typing them in the Haedings Property of the Query.
 

3 more replies
Answer Match 51.66%

Our objective was to get the field to populate throughout the datasheet table. Meaning that

Field 1 Field 2 Field 3 Field 4
1111 Contract Apples Carrots
1111 Contract Soups Celery

Getting and Maintaining Field 1 & Field 2 to stay the same throughout the table
Since my brain is fried today and its already the middle of the week. Any suggestions?
 

A:Access: Record Keeping. Same Fields

12 more replies
Answer Match 51.66%

I will be working with an extremely large database and am wondering if the maximum number of fields,in MS Access (255),is absolute. I may also have several tens of thousands of records. Can the program be tweaked to allow for more fields? Is it RAM dependent?
 

A:Maximum Number of Fields in Access

IMHO:

I strongly suggest that you never create a table with that many fields. Instead, think about how you are laying your data out. Access is a relational database where specific data should be kept in individual tables.

For instance, a payroll database should contain several tables, including: personal employee information (empID#, SSN, Name, Address, etc.), payroll info (empID#, PayLevel, amount of pay or range of pay), gross pay by period (empID#, date paid, gross amount, taxes withheld, net amount), etc., and they would all be related by the empID# field.

By doing this, you should have a much more manageable amount of data in multiple tables.

You can email me a sample of your data if you want. Just make sure there's column headings and about 100 records would be great.

I've emailed you a document about "normalization," a term used to describe the way to set up tables in a relational database. Hope it helps!
[This message has been edited for tactfulness by Dreamboat (edited 03-29-2000).]

[This message has been edited by Dreamboat (edited 03-29-2000).]
 

2 more replies
Answer Match 51.66%

Hello All.

I am working on a piece of code to check what field names are in a query and then I need to pass those field names into another query.

I found this code here on the forum and it gives me field names but I need to skip a few known field names as well.

Any ideas?

Sub FieldNames()
Dim Rst As Recordset
Dim f As Field

Set Rst = CurrentDb.OpenRecordset("qryAccessorial_CrossTab")

For Each f In Rst.Fields
MsgBox (f.Name)
Next
Rst.Close
End Sub
 

A:Getting fields names in Access 2007

13 more replies
Answer Match 51.66%

According to what I've read, Access 2010 uses the same database format as Access 2007. So this should mean I can open and run a 2007 database (with a lot of VBA code) in Access 2010 with no trouble, yes?

So why do I have the feeling that something is not going to work? Is it because I have been through to many Access upgrades?

A:Any problems using Access 2007 database with Access 2010?

Hi bbinnard,

If you have not purchase Access 2010 yet, what I'd recommend is....download the Trial Version and play around with it for quite a while.

4 more replies
Answer Match 51.24%

Hello,

I couldn't find the answer answer anywhere, so hope someone here can help.
I have several tables with info about partners, subsidiaries, contracts etc.
They all come together in a single table with the name project. Since 1 partner can participate in many projects I need to know how I can set up that my form for the table projects, with subforms partners and contracts etc fills in the data (name etc) for partners automatically when I only choose the partner ID from a list box.

Thanks in advance!
 

A:Access, trying to autmatically fill in fields after chosing ID

Can we have a look at your table structure?
 

1 more replies
Answer Match 51.24%

Can anybody point me in the direction of the best help and instructions for adding fields (which will contain formulas) into MS Access 2016 reports.

Step by Step or Youtube would be preferred.

Am not VBA proficient and am learning Access.

T

More replies
Answer Match 51.24%

Form has several calculated fields. The first part of this code works fine, the second part that is supposed to calculate the Reservations Amount Owed does not work - the field returns blank. I also have another calculated field that isn't working: Reservations Balance Due which would be Reservations Amount Owed minus Reservations Amount Paid. What am I doing wrong? Thanks in advance for your help.

Private Sub Combo68_AfterUpdate()
If Me.Special_Ticket = "Corporate_Table" Then
Me!Ticket_Price = "312.50"
Else
If Me.Special_Ticket = "Sweetheart_Ticket" Then
Me!Ticket_Price = "800"
Else
If Me.Special_Ticket = "Patron_Ticket" Then
Me!Ticket_Price = "350"
End If
End If
End If
End Sub

Private Sub Reservations_Amount_Owed_AfterUpdate()
Me.[Reservations_Amount_Owed] = Me.[Reservations_Number_of_Tickets] * Me.[Reservations_Ticket_Price]
End Sub
 

A:Access 2007 form calculated fields

16 more replies
Answer Match 51.24%

I have a Table in Access 2010 which has several true/false fields. Is there a way for me to pull a report that lists which fields are true for each record while not listing the other fields?
 

A:Access 2010, List True Fields

8 more replies
Answer Match 51.24%

I have a query that has a couple of text fields where in the criteria I put <>"Power". When I put this criteria in, it also does not show me records that the field is blank. If I leave the criteria empty, it shows me all records including the records with the blank fields. How do I get the query to exclude the criteria Power but still show records where that field is blank?
 

A:MS Access Query not returning blank fields

16 more replies
Answer Match 51.24%

We have an Equipment Inventory database and we would like to change the sorting priority in two fields-example:
Equipment - ascending first priority - current
Location - ascending second priority - add
Description - ascending third priority - add

The report is already created and when I have went out to add the changes it doesn't change the order on the report. Not sure what I am doing wrong.

Any suggestions?
 

A:Access 2003 sorting multi fields

11 more replies
Answer Match 51.24%

Hi,

I have two tables in Access 2007.

In table one I have a field which includes postcodes. In table two I have a field which includes the first three digits of a postcode (e.g. IP7).

I want to do one of two things:

Either match the first three digits of the postcodes in table one to the partial postcodes in table two;

or

Split the postcodes in table one so that I have a new column with the first three digits (i.e. using the space as the separator) - I can then match it with the other table.

Can anyone help?

Thanks,
George
 

A:Split a column in Access or match fields

9 more replies
Answer Match 51.24%

This is how the DB looks like:

tbl 1
ID Name
123 XYZ
145 ABC

tbl 2
ID Docs
123 Paper 1
123 Paper 2
123 Paper 3
145 Paper 1
145 Paper 2

I am trying to build a query that works with Mail merge in Word that returns:
ID Docs
123 Paper 1, Paper 2, Paper 3
145 Paper 1, Paper 2

I have managed to do this with VB and it works very well but it gives me "An error has ocurred, undefined function" when i open word to mail merge.

Can you help me, guys ? I'm kinda stuck here.
Thank you!
 

A:Creating a query with concatenated fields in Access

12 more replies
Answer Match 51.24%

I running access 2003 but i am creating for 2000

Bsaicly its a stock control system. And i have a field call "Total number ever in stock" Which basicly refers to how many of such a product has been purchesed for the shop altogether.
Reasons for this are complicated but its stems from the batch procecsing method of data colection but its not important.

>>>>>>>>
Simply i want to create a macro or simmilar so that when the user enters a Product ID and Quantity to be added into a blank non table related form the macro will add that quantity to the existing quantity for that product in a table.

So far i have created a form in design view and have inserted two unbound text boxes "ProdID" and "Amount" and have a table with field names "Product ID" Total number ever in stock"
<<<<<<<<

Thanks all help appreciated.
 

A:Adding to fields through use of macro and form - ACCESS

If I understand you correctly, you have an unbound form that a user enters a product ID and qty to be added to a related record that already stores a summary amount. You can do this using that method, but it will have some data integrity challenges. If someone enters the wrong amount, you won't know where it came from or how much the qty was for the wrong amount.

To perform the update from an unbound form can run an update query from a button.

Here's a SQL sample that you could run from a button

UPDATE Prod_Totals SET Prod_Totals.Product_ID = [Forms]![frmQtyUpdate]![txtProdID], Prod_Totals.Total_Qty = [Forms]![frmQtyUpdate]![txtTotQty]+[Total_Qty];

---------------

That being said...I would consider approaching it from a different angle.

1. Create a table of Item_Events and Products

Table Name:

Item_Event
Item_Event_ID (Autonum) Key
Product_ID
Qty (+/-)
Event_Type (Purchase / Sale / etc..)
Event_Date

Products
Product_ID (key)
Product_Description
Create a form based on Products with a subform of Item_Events. When you need the total qty use a summary query.
 

2 more replies
Answer Match 51.24%

(When I go to edit a field definition using Expression Builder) Expression builder does not contain any reference to tables\fields (only functions, constants and operators), which means I can not edit a field definition to reference other fields (not a lookup). What am I doing wrong?
 

A:no fields in Expression builder - Access 2007

You do not see fields in the Expression builder until you save your query with the fields in there. It is a little nuisance that has been in Access for many years
 

3 more replies
Answer Match 51.24%

I have one table that has information on all my gears, called tblGearFeed. This table has 3 columns of information (CAM, Feed, and Gear A)

I am using a form called frmQuotation.

I have "CAM" and "Feed" fields that users will fill in. Based on these two fields, I need the "Gear A" field to lookup the cooresponding data from tblGearFeed.

I have been trying to use the following function:

=DLookUp("[Gear A]","[tblGearFeed]","[CAM]=Forms![frmQuotation]![CAM] And [Feed]=Forms![frmQuotation]![Feed]")

This formula is not working.
Can anyone guide me as to what I am doing wrong?
 

A:Solved: Access- DLookup based on two fields

If you do an AND it will only bring up the values in each field that equal your parameters. Use an OR instead.
 

3 more replies
Answer Match 51.24%

Ok...I know there is a simple way to do this but can't come up with it.

I would like to check multiple Combo Boxes (40) to see if the same value has been entered. In the example below it is "ClassEquivalent". (The same code would work for a NULL value) If the value is True then I would like to display a msgbox. I know that there is more simple code than this...and I only want one Message Box to be displayed if any of the Combo Boxes contain that value.

If Me.ComboBox1.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox1.forecolor = 255
Else
If Me.ComboBox2.value = "ClassEquivalent" Then
MsgBox("Please Fill in Type of Equivalent",vbcritical,"ERROR") =vbok
Me.LabelComboBox2.forecolor = 255
etc.....
 

A:Solved: MS Access: Check Multiple Fields for One Value

There is a set of videos here that show you how to unlock and lock a group of controls, from a basic method setting each control individually, right up to using a class module that monitors the command button and takes action. I think you could adapt this code to work for your 40 Controls. The videos about the class module method are the videos numbered 5 6 and 7.

Cheers Tony
 

2 more replies
Answer Match 51.24%

I have a complex report(in Access that comes from multiple tables and groups on multiple values. The report works fine. Where the problem comes in is because I added a necessary field to the table that also needs to be added into the report. This field does not show up in the view fields panel and thus I cannot figure any way to make this report other then copy and pasting all the reports contents into a new report along with the code from the report. As this is a large nuisance is there any other way to accomplish this? Somehow possible to access the report wizard to add this field?
 

A:Adding Fields to complex report(Access)

8 more replies
Answer Match 51.24%

Experts,
Usually iin MS ACCESS 2010 when you create a field in a table for attachement, the sub fields are there in the " Add Fields" top a form but this time after entering the field of Attachment in a table , i dont see any of the subfields. Did I miss something here.
Your help is appreciated.
 

A:Solved: MS Access 2010 sub fields attachment

Sorry, I have never used Access 2010 or it's Attachments field.
 

2 more replies
Answer Match 51.24%

Hello, I'm trying to figure out a way to calculate time fields in a database, I would like to know how to add time fields from two text boxes so that the amount of hours will display in a Total Hours text box. I am using two text boxes, Start Time and End Time, Total Hours is where the total will be displayed in amount of hours from the two time fields, an example would be 8:00 AM (in Start Time) and 10:30 AM (in End Time) and 2.5 being in Total Hours. On a form, when typed in the Control Source property for Total Time, =DateDiff("h",[Start Time],[End Time]) seems to work, but it is not inputting the sum in the main table where all of the fields are kept, is there any way the total can be done and kept in the main table? Also, I would like to include a Total Hours text box with the totals of the Total Time fields because I would like to add more text boxes on a form. Can somebody help?
 

A:Access 2000 Calculating Time fields

That's the only place you can do it is in a report or query. I don't think you can calculate directly in the same table for fields within that table. Try creating another table and calculate the date fields within that table. Is there a reason this time difference needs to be stored in a table?
 

2 more replies
Answer Match 51.24%

I need to open a form and show only where a field in the subform is equal to a value the user gives. The problem I am having is referencing the field in the subform. Anyone know how to do this?
 

A:Access: Referencing subform fields in Docmd Where

8 more replies
Answer Match 51.24%

I have multiple fields in my table, two of which are called Customer_ID and Supplier_ID. They are just text fields. What I need to do is make sure only one of them is filled in each record. In other words, how do I constrain 2 fields to keep one from being filled when the other already has something in it? I'm using access 2007 on windows xp.

Thanks!
 

A:Solved: Table fields, one or other, Access 2007

8 more replies
Answer Match 51.24%

Hello all,
I have a db where I have a list of projects. The projects are separated into two tables with the first containing information about the client and a related table with information pertaining to the individual projects done for each client. This is a one to many relationship.

I am currently trying to establish a resume creator for my db where I would have an employee table which would be related to individual client's projects. My goal is to create a form that I can select what projects each employee worked on and give a report on all the information given in the client and project tables for the projects any particular employee worked on.

What I cannot figure out is how to relate the employee table. I can't do it to just the client table because the employee is not on every project the client has and I cannot relate it just to the project table because I need the client information in my report as well.

Any help would be greatly appreciated,
Madctch
 

A:Solved: Access: Bind to multiple fields

6 more replies
Answer Match 51.24%

Hi all,

I need help on hiding no data fields in reports and those fields should be replaced by next field.

plz help me out in this

thank you
 

A:Hide no data fields in access reports

10 more replies
Answer Match 51.24%

Hello,

I have multiple tables that has info as such:

table_1:
f_name | l_name
bob | simth
john | doe

Than I have another table (same database)
table_2:
f_name | l_name
mike | brown
me | stupid

How do I combine them so I get:
table_All:
f_name | l_name
bob | simth
john | doe
mike | brown
me | stupid

Any help is appreciated
 

More replies
Answer Match 51.24%

Hi folks. I work in the IT field but am not too good with MS ACCESS 2003. I have been given a task by my boss to get familiar with MS ACCESS. I was wondering if anyone here knows how to populate data from another table in MS ACCESS 2003? I'll give you a description. The client has a form called Referal form, Primary Referal Source and Secondary Referal source fields that need to have data populated from a referal persons table. Can anyone tell me how I can go about doing this.Thanks in advance.
 

More replies
Answer Match 51.24%

I am trying to create links to specific locations with each document between two documents. The document names will change. I can update the current name of the other document by creating a custom property called "otherDoc". I have solved most problems, however, I am unable to insert a field into the address of the link (to make the link dynamic) without utilizing the showfieldcodes command (which is very memory and time consuming since the documents I am working on are very large). Below is my code. I was wondering if there is any way to set the address as a field? Or somehow input an address and it would be understood by word to be field.
Sub link_to_other()

'pastes hyperlink to the other document
Selection.PasteSpecial Link:=True, DataType:=wdPasteHyperlink, Placement:= _
wdInLine, DisplayAsIcon:=False

'selects the hyperlink that was just inserted
Set myField = Selection.PreviousField

'creates variables
Dim temp_address As String
Dim temp_subaddress As String

'sets variables
temp_address = ActiveDocument.CustomDocumentProperties("otherDoc").Value & ".doc"
temp_subaddress = Selection.Range.Hyperlinks(1).SubAddress

'changes the hyperlink the the dynamic value
Set SCut = ActiveDocument.Hyperlinks.Add( _
Anchor:=Selection.Range, _
Address:=temp_address, SubAddress:=temp_subaddress)
End Sub

 

More replies