Problem Incrementing record field

[jnorris]

I thought htis would be fairly simple, there I go thinking again! Anyway, I have a field called "ETR" in a table called NEWETR1 (this is a string in the format 2003-0001)that i want to increment whenever a new ETR is opened, or in this case whenever this form is opened and then have the data on this form saved back to NEWETR1 as a new record.
To start in ETR properties in Default Value i put ="2003-"+Str(Val(Right(DMax("ETR", "NEWETR&quot,4) +1))
This works but it returns the etr number without the leading zeros i.e. 2003-10 instead of 2003-0010. Also even with this number and I do a requery and it doesn't save to NEWETR1 table??
Any help on this would be greatly appreciated.

Jim

 

[PWise]

try
="2003-"& format(Val(Right(DMax("ETR", "NEWETR&quot,4) +1),"0000&quot
what do you mean by "and it doesn't save to NEWETR1 table"

 

[LittleSmudge]

Why not 'atomise' the data ( Normalisation process ) into ETRYear and ETRInteger

Then simply increment the ETRInteger
and set ETRYear = Year(Date())

Then, when you need to display the ETR number on a form or report use a text box control txtETR

txtETR = Trim(CStr(ETRInteger)) ' Set txtETR to integer part
WHILE Len(txtETR) < 4
txtETR= &quot;0&quot; & txtETR ' Keep adding zeros until length = 4
WEnd
txtETR = Trim(CStr(ETRYear)) & &quot;-&quot; & txtETR ' Add Year to the front



'ope-that-'elps




G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.