Generate Number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to generate a number based off of the next record. basically if
i have 300 entries, then on the new record i want the new one to be 301. my
format is like yy-000 number with yy being the year. i also want this number
to reset to 000 when the year changes over. i have seen some info on here
regarding this but i can never seem to make it work, any info would be
greatly appreciated.

Chris
 
Hi,
you can use a following formula:

=Val(mid(Nz(DMax("MyField","MyTable", "Left('MyField',2)=" &
year(date()))),3))+1

something like this, hope this will give you an idea
 
if
i have 300 entries, then on the new record i want the new one to be
301. my format is like yy-000 number with yy being the year. i also
want this number to reset to 000 when the year changes over.
Click to expand...

You have two separate bits of information here, so you really need two
fields. Make the first one YearNumber (integer) and the other one
SerialNumber (integer). You can create the PK on the combination of the two
(at very least I assume that you'll need a unique index on them).

You can get the next number for the given year using a DMax() function,
pretty much as Alex suggests.

Remember to present the number to the users all concatenated up, so they
don't know about the underlying structure:

ControlSource = Format(YearNumber,"00\/") & Format(SerialNumber,"000")

Hope that helps


Tim F
 
Back
Top