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!