Sequential Numbers

[cactuspete13]

Not sure if this is in the right section or not but here it goes. I am trying to build a field using an update query that pulls information from several fields to create a unique identifier for each record. I have the entire thing working except for one thing, an autonumber function. Here's how I'm trying to get it to work:



Main is a table that contains all of the records. From there I have an update query that pulls all the other information I am using (dates mostly) to build the final code. What I need is for the update query to pull all records matching the criteria from one field, and assign a number (two different criteria, one will require a 3 digit number starting at 001, and another requiring a 4 digit number starting at 0001) so I can plug it into unique identifier.

[June7]

Here is recent thread discussing generation of unique id https://www.accessforums.net/forms/a...ity-33608.html

[cactuspete13]

June7, thanks but that confused me beyond belief. Let me try explaining better what I am trying to do. What I'm trying to get the update query to do is: pull the location and assigns a number specific to that location (working), looks at the date and pulls a two digit number for month and year (working), assign a sequential number starting at 000 and adding one (not working) then throw it together as one identifier. The Update To line on the query looks like this:

[LocationCode]&""&[MonthCode]&""&[YearCode]&""&[IdentifierCode]

So, a location with a code of 1, with today's date, and being the 5th record would look like: 10313005. A record entered in an hour, with the same location code would look like 20313006.

I've tried setting the Update To line to {DMax("[SRNCode]","SARCMain")+"1"} (minus the {}) but all it does is change 001 to 0011

I've attached an extremely slimmed down copy of the database. If you open the table Main, you will see the first column is empty on all six records, the records from 4 Jun 12 and 17 Feb 13 have an SRNCode of 001 and 0001, respectively. Run the queries RNUnresUpdate and RNResUpdate then refresh the table to see that the all records now have a series of numbers in the first column (the two marked Restricted follow a different layout), with the first column showing the full desired product. I am trying to get the remaining records with Unrestricted as the ReportType to follow suit: 20912002, 31212003, etc, and the Restricted records will have the "000x" right before the "RR".

Any help would be greatly appreciated.

sampledb.zip

Disregard, I figured out a really ugly way of solving the problem but it works. Thanks.

[June7]

Sorry, I didn't catch the requirement to update multiple records.

The + character is also a concatenation operator - leftover from old BASIC. Since all the values in that expression are strings, the operation is concatenation, not addition.

This expression in the UPDATE will generate 001 for all records, it does not increment: Format(Nz(DMax("[SRNCode]","Main"),0)+1,"000")

AFAIK, cannot run an UPDATE query to generate an incrementing ID for all records. Will have to be VBA procedure to modify one record at a time in a recordset using a loop structure.

Do you want the increment to start over each year? Why do Restricted and Unrestricted need to be different sequences? Aren't 'Restricted' and 'Unrestricted' adequate classifiers? What should happen when you hit 999 and 9999?

[Rawb]

Personally, I hate the "D" functions and try never to use them. That said, you might be able to get this to work using DCount() instead of DMax().

However, my personal recommendation would be to use a Subselect to count the number of Records that show up before your "current" Record.

[June7]

I stand corrected. Rawb is right.

DCount works.

Format(DCount("*","Main","ReportDate<#" & [ReportDate] & "# AND ReportType='Unrestricted'")+1,"000")

Format(DCount("*","Main","ReportDate<#" & [ReportDate] & "# AND ReportType='Restricted'")+1,"0000")