daily incrementals

[samonwalkabout]

I need to build a numbering system for new records in a database that combines. month date and ticket raised in day. So today would be 2/9/ and then the ticket raised number 2/9/1 for the first 2/9/2 for the second ect ect. I need to use this method as its replacing a current system and they want to keep the format the same, this is really for display purposes and a different auto number field is the primary key.

I can generate month and day but how can I get the last part of the code to increase incrementally through-out the day and reset to 1 on the start of the next day.

Many thanks

 

[dcx693]

Do you need this going forward, or back in history?

If just going forward:
You must have a date field in your table, so you can use a Dcount function with the current month and day as criteria to see how many numbers were assigned today. Use some code in your forms Current event to see if a number has already been assigned to the current record. If not, update it using the Dcount function.

Historically:
Use some DAO or ADO code to go through each record and assign the numbers using the Dcount function. Of course, if you have a really small number of records, you can also use the above method, but you'll have to visit every record on your form.

 

[samonwalkabout]

Works a treat


Thanks

 

[dcx693]

Durn it! Why the heck do I keep saying DCount?!!?? Crap. Crap. Crap.

salmon, Pat is is right. Use DMax!

 

[samonwalkabout]

Okay i will change it DMax no worries. Also combining in the date part of the ref number generation it there any way to get the date in American format mm/dd/yy, even though the rest of the DB will not be (DD/MM/YY).

Im currently getting around it with 3 DatePart txtboxes then recombing them.

 

[Rich]

Use the Format function

 

[samonwalkabout]

i dont have a American Date format in my format options, do i need to apply in code?

 

[samonwalkabout]

DOH!!!! ignore me i just figured out what ya mean. Works great in the first box but.....

i am taking this txtbox called dater2 and combining it with a ticket number thus

=[Dater2] & "/" & [ticknum]

the date formating goes back to the standard english?

 

[dcx693]

Ok, I'm counting now

Pat, I'm going to go back and delete my earlier posts , that way a Dcount of them will produce a much lower mistake count than a Dmax!

 

[samonwalkabout]

Thanks Pat thats fixed it.

Need to keep it MM/DD/YY as this is the same as there current "system" (about 400 books), so unless there's a fire i have to stick to some of the old conventions but its not a key field it just for display purposes.