 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / January 2004Create number based on current date
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 =?Utf-8?B?VkFE?= <anonymous@discussions.microsoft.com> wrote in news:8FBA568F-069A-49F7-B76A-CC6C5B9B1CAC@microsoft.com: > 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 >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 John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.