Create number based on current date

[VAD]

I am trying to generate a number for my HelpDesk table based on the current date. I would like the numbers to look like this: 0116040001 and then increment by one so not to have any duplicates. Then on the next day the ticket number will be that days date, 0117040001 or where the last four numbers left of the day before. It will be nice if the next days number could start with the last 4 digits starting at 0001 but if that is to difficult then I don't need to do it that way. I see in some of the posts I have read people referring to the DMAX function. Is this what I need to use and how would I write it
In the past I have used the AutoNumber field with success but didn't like tickets numbers like 1, 2 10. On my last job I was able to set the starting number in the field which was better than before but not what I really wanted
Thanks in advance for your help

 

[Tim Ferguson]

I am trying to generate a number for my HelpDesk table based on the
current date. I would like the numbers to look like this: 0116040001

This is what is called a Really Bad Idea. If you have two pieces of
information (such as a CallDate and SerialNumber) then store them
separately. You'll be so glad you did -- check out First Normal Form in any
primer on DB Design.

I see
in some of the posts I have read people referring to the DMAX
function.

Yes. Google for "Access Custom Autonumbers" or start at Dev's site
<http://www.mvps.org/access>

HTH


Tim F

 

[John Vinson]

I am trying to generate a number for my HelpDesk table based on the current date. I would like the numbers to look like this: 0116040001 and then increment by one so not to have any duplicates. Then on the next day the ticket number will be that days date, 0117040001 or where the last four numbers left of the day before. It will be nice if the next days number could start with the last 4 digits starting at 0001 but if that is to difficult then I don't need to do it that way. I see in some of the posts I have read people referring to the DMAX function. Is this what I need to use and how would I write it?
In the past I have used the AutoNumber field with success but didn't like tickets numbers like 1, 2 10. On my last job I was able to set the starting number in the field which was better than before but not what I really wanted.
Thanks in advance for your help

I'd strongly suggest *not* storing two pieces of information in one
field in this way. Instead, you could use a *two field* key consisting
of the date, using Date() as a Default value; and a Long Integer
field. These can be concatenated for display purposes with an
expression like

[CallDate] & Format([CallSeq], "0000")

To assign the integer portion, you'll need to do all your data entry
using a Form; in the Form's BeforeInsert event put code like

Private Sub Form_BeforeInsert(Cancel as Integer)
Dim lngNext As Integer
lngNext = NZ(DMax("[CallSeq]", "[HelpDesk]", "[CallDate] = #" & Date()
& "#")) + 1
If lngNext > 9999 Then
MsgBox "Go home, you've answered too many calls already", vbOkOnly
Cancel = True
End If
Me!CallSeq = lngNext
End Sub

 

[Vito]

Tim and John,
Thank you both for your replies.
Tim, I did do that Google search and couldn't find what I was looking for. There were some results that were close to what I am trying to do but not exactly.

John, thanks I will try that code. I have written many versions of my database but never used any modules or any other type of code to accomplish any tasks. I use a ton of macros with great success for simple tasks of populating data/time, yes/no fields and others. I am no code writer but know I could write a better database if I knew how.
Thanks again to both of you.