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

 

[Pat Hartman]

DCount() is risky for this purpose since it doesn't acount for the possibility of deleted records. ALWAYS use DMax() when assigning your own sequence numbers. It always gives you the highest number previously used. DLast() is also inappropriate since DLast() returns the last physical record in the recordsource which may or may not be the record with the highest sequence number.

 

[dcx693]

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

salmon, Pat is is right. Use DMax!

 

[Pat Hartman]

Ok, I'm counting now

 

[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!

 

[Pat Hartman]

You need to format the date any time you use it.
=Format([Dater2],"mm/dd/yy") & "/" & [ticknum]

Although the following would be better for sorting:
=Format([Dater2],"yy/mm/dd") & "/" & [ticknum]

 

[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.