VBA code to generate sequential report numbers

[jspisak777]

I have a database in which I want to automatically assign numbers to records in the format 4 digit date,two digit month, two digit date, then end with an - XXX. So each record would have a number that looks like this 20070917-001, -002.... and would automatically update year. I've tried a tweaking couple of code examples I found on the web, but no success. Any ideas?

[asingh]

Hi,

You could try ..creating an autonumber field on your table..which would increment by 1 each time a record is updated..and then using a simple query.with a concatenate....combine it with your date fields.....

So if you have four fields...year,month,day,autonumber -- your concatenated field would be:

year&month&day&"-"&autonumber.

This this will not give the format -001, 002....if you want that..then you would need to evaluate the length of the autonumber..first....

regards,
asingh