Incremental Numbering with a Constant Included

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

Guest

What kind of title is that? I'm chasing my tail trying to get a field in a
form that looks like this...2007-001, the next number being 2007-002, and so
on. The field name is NewTrackingNumber; the table is Project Management
Data. O great gurus of all that is Access, please help!
 
Create a query to return the Max number from the number that is on the right
side of the "-"

SELECT Max(Val(Mid([NewTrackingNumber],InStr([NewTrackingNumber],"-")+1)))
AS MaxTrackingNumber
FROM [Project Management]

Then, to retrieve the next number you can use
="2007-" & Format(DlookUp("MaxTrackingNumber","QueryName")+1,"000")
 
Okay...bear with me while I reveal my inexperience! How would I build this
code into a query? Did you perhaps mean a macro?

I entered the code into the 1st level criteria box in the Query design. Then
I went to the form and added the argument to the Control Source for the
field. When I open the form in user mode, the field has #Error in it. Yes, I
remembered to change the "QueryName" in the argument to "NewTrackingNumber"
(what I named the new query). I'm sure this is not as hard as I'm making it.
Please, please guide me to towards the light...

Ofer Cohen said:
Create a query to return the Max number from the number that is on the right
side of the "-"

SELECT Max(Val(Mid([NewTrackingNumber],InStr([NewTrackingNumber],"-")+1)))
AS MaxTrackingNumber
FROM [Project Management]

Then, to retrieve the next number you can use
="2007-" & Format(DlookUp("MaxTrackingNumber","QueryName")+1,"000")


--
Good Luck
BS"D


Giggly4g said:
What kind of title is that? I'm chasing my tail trying to get a field in a
form that looks like this...2007-001, the next number being 2007-002, and so
on. The field name is NewTrackingNumber; the table is Project Management
Data. O great gurus of all that is Access, please help!
Click to expand...
Click to expand...
 
Look at this link on "Automatically Increment a Fields Value in a database"

http://www.databasedev.co.uk/automatically_increment_value.html

Mybe this link together with the example I gave you will step you through

--
Good Luck
BS"D


Giggly4g said:
Okay...bear with me while I reveal my inexperience! How would I build this
code into a query? Did you perhaps mean a macro?

I entered the code into the 1st level criteria box in the Query design. Then
I went to the form and added the argument to the Control Source for the
field. When I open the form in user mode, the field has #Error in it. Yes, I
remembered to change the "QueryName" in the argument to "NewTrackingNumber"
(what I named the new query). I'm sure this is not as hard as I'm making it.
Please, please guide me to towards the light...

Ofer Cohen said:
Create a query to return the Max number from the number that is on the right
side of the "-"

SELECT Max(Val(Mid([NewTrackingNumber],InStr([NewTrackingNumber],"-")+1)))
AS MaxTrackingNumber
FROM [Project Management]

Then, to retrieve the next number you can use
="2007-" & Format(DlookUp("MaxTrackingNumber","QueryName")+1,"000")


--
Good Luck
BS"D


Giggly4g said:
What kind of title is that? I'm chasing my tail trying to get a field in a
form that looks like this...2007-001, the next number being 2007-002, and so
on. The field name is NewTrackingNumber; the table is Project Management
Data. O great gurus of all that is Access, please help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top