Counter In Update Query

[Faction21]

I need a field to update to the next number starting with the number the user enters. The field Is Called Record_Num and it needs to be text datatype, not AutoNum. I am using an Update Parameter query to ast the user for the first number, but I cant figure out how to make it Count of the number and add 1 for the next record.

This is what I have so far:

UPDATE tblSummation SET tblSummation.Record_Num = "; Record " & [Enter Record Number:]
WITH OWNERACCESS OPTION;

How can I get it to add to the number entered by the user? So that the next record is updated to:
; Record 2
; Record 3

and so forth.

Any help?

-Thanks

 

[Faction21]

Any one have any ideas?

or is this more like a VBA question?

 

[mresann]

I don't know why you are storing duplicitous data (such as 'Record ') in the table. Consider basing your data reporting methods using queries. This way, you can take a number column and display it using the AS protocol. For instance, if the number in the column is 2, the name of the query column would be

'Record ' & [tablename].[actual_number] AS Record_Number

(In the query grid, the format is as follows:

Record_Number: 'Record ' & [tablename].[actual_number]

Therefore, a column which has a numeric value of 3 is returned 'Record 3'.

This way, if you need to update a record, all you have to do is to use the max function to determine the highest value in the field, then update it with that value + 1.

Your solution, though, requires much more string manipulation. For instance, unless you have other columns which can sort the records in the order you want (such as the aforementioned actual_number column), you will have a bear of a time calculating which value in the Record_Number field actually contains the highest record number. Remember, in Text mode, instead of numbers being in order numerically (such as 1,2,3,4,5,6,7,8,9,10,11,12), the order is determined alphabetically (actually, through character set asssignment). The order would be 1,10,11,12,2,3,4,5,6,7,8,9) This would not bode well for calculation.

Further on, you will have to use character identification functions or string manipulation functions to parse the number out of the name, then to convert the text number to a true number format, then to increment it, then reconvert the new number to a text format and cocatenate it with the text before it can be saved in the table. Huge amounts of code, and frankly destroys the normalization process of the tables.

The SHORT answer: Change your Record_Number field to a number field, do not include 'Record ' in the field, and use a query to show 'Record ' & number as shown above.

The LONG answer: Sorry, I got a life