Adding last 2 digit of year to an autoincrement field on form

[BPBP]

Auto increment textbox on the form is [Job No].
Default value of this textbox = Dmax("[Job No]","[QT Job Record]")+1

This autoincrement starts from 1, 2, 3... ...

1st Question- I would like this field to be stored as a 6 digit number, eg. if the data is 3, then it should store as XX0003, or 300(stored as XX0300)
Then I would like to append the last 2 digits of the year as the 1st 2 significant digits (XX above), ie for year 2009, and data field is 3, it stores as 090003, or year 2009 and incremented field is 300, store 090300.

I could from the start, start the 1st entry of the table as 090001, but this is not a good idea.

please suggest some possible ways to do it so that this would be automatic and not need to manually start it every year?

 

[RuralGuy]

Consider keeping all of the fields separate and just displaying them put together.

 

[BPBP]

i need to search by this job number though. because the job number is restarted every year hence different years would have same id number so the year appended in front helps differentiate which year it is from.

 

[RuralGuy]

You can search on concantenated fields. What's the problem.

 

[BPBP]

well the main problem is i'm unfamiliar with how things should work, especially the part where if i were to use a textbox to search and display records based on concantenated fields...

Okay lets say i split into 2 fields, 1 is for the year no, the other is for the job no.
For the year no, how do i get it automatically just fill in the last 2 digits of the year based on system date eg. this yr is 09. (i only know = date() but this gives everything ), i only require the last 2 digits. Is there some function for this?
For the job no. field, how do i ensure it stores as 4 digits? eg. 0001, 0002 etc... or do i even need to store the zeroes? could I format it show automatically show 4 digits, ie: if job no is 30, it will show as 0030, if it is 312, it will show as 0312?

If i dun't split into 2 fields, it goes as 090001, 090002 etc...

 

[Mike375]

Right(Year(Date()),2) will give the last two numbers of the year and will automatically update.

[LastName] & " " & Right(Year(Date()),2) will add last two numbers and with a space between name and numbers. The list is endless

You do what you like with joined fields for search etc. just treat as normal field. And of course you could have the joined data update a table field.

But you will need joined fields if the last two digits of the year is to be automatically the correct year.

 

[DCrake]

Once you have established what the next sequential number is, say 321, to convert 321 in to 090321 you need the following code

Format(Date(),"yy") & Format(NextID,"0000")

Where NextID is the next number. Consider if 999 is not going to be exceeded in the current year? may need to up it to 0900321. Better to be safe than sorry.


David

 

[BPBP]

Once you have established what the next sequential number is, say 321, to convert 321 in to 090321 you need the following code

Format(Date(),"yy") & Format(NextID,"0000")

Where NextID is the next number. Consider if 999 is not going to be exceeded in the current year? may need to up it to 0900321. Better to be safe than sorry.


David

exceeding 1000 is likely but not possible to exceed 1500. but with 4 zeroes I should have up to 9999 right?


For the [Job No] default value property i set = Format(Date(),"yy") & Format([JN],"0000")
where JN is the autoincrementing field. for next sequential number say 321, I obtained 90321 instead of 090321? The date portion displays as 9 instead of 09? The rear zeroes portion worked okay though.

I realised this is due to data type as number. I can change it to text, but would prefer to keep it as a number, as i may need to do some arithmetric on it later on. How do i make the portion display the 0 in front? i'm fine with it being stored without the zero in front.

 

[DCrake]

Any leading zeros will be truncated if you want o retain it in numeric format. If you change it to text you will retain the leading zero and will have a perfectly ordered sequence. if you want to perform numeric equasions later then all you need to do is use Val([FieldName]) in your query.

David

 

[BPBP]

okay. thanks all.