X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Unique Alpha-date-number Formatted Tracking Designator, Access 2013    
post Jan 11 2018, 10:09 AM

Posts: 4
Joined: 11-January 18

I am working on a database where I need to do something I have never done. My client wants a numbering system for her database to track loads by a unique number. The format she would like to use is a three letter prefix (this prefix will never change) with the date formated mm-dd-yyyy followed by a number that counts the number of loads for that day, i.e. XXX-01-11-2018-001 for the first load that day and the second load would be XXX-01-11-2018-002. The next day the unique number for the loads that day will be XXX-01-12-2018-001. Ideally this number would auto populate when creating a new load. This number will need to appear in queries and on reports. Does any one have an idea how I can make this work?
Go to the top of the page
post Jan 11 2018, 10:47 AM

UA Admin
Posts: 32,583
Joined: 20-June 02
From: Newcastle, WA

Welcome to UtterAccess.

First, given the fact that the initial prefix is, indeed, fixed and will never change, you should NOT include it in the unique number field. Instead, it should be in a field all its own, if at all. I'll get back to that point later.

Now, you can create a small Function to generate the unique sequence values to be CONCATENATED into the Load Tracking Number each date. Here's how that might work, aircode to illustrate the idea.

Public Function DailyLoadNumber() AS String

    Dim strTempLoadNumber as String
    Dim lngCurrentLoadSequence as Long

    strTempLoadNumber = "ABC" 'This is the three letter prefix. It does not need to even be in the table, as it will never change.
    strTempLoadNumber = strTempLoadNumber & "-" & Format(Date, "mm-dd-yyyy") ' This concatenates the prefix to the string representing the current date.
    lngCurrentLoadSequence = NZ(DMax("DailySequenceNumber", "tblYourTableNameGoesHere", "tblYourTableNameGoesHere.WorkDate = #" & date & "#"),1) 'If there are no existing load numbers for the current date,
             ' start with 1, otherwise get the largest existing one for today.
    lngCurrentLoadSequence =lngCurrentLoadSequence  +1 ' increment the last load number for the next one
    strTempLoadNumber = strTempLoadNumber  & "-" & Right("000" & lngCurrentLoadSequence,3) 'Append the new load number to the previously created prefix and date, and pad it with leading zeroes.

    DailyLoadNumber= strTempLoadNumber

End Function

Call this function to get the next available value for the current date.

Note that you don't need to store values that will never change, i.e. the prefix. HOWEVER, one thing most of us have learned over years of Access work, is that things that are not supposed to change, ever, sometimes do change.

Go to the top of the page
post Jan 11 2018, 11:30 AM

Access Wiki and Forums Moderator
Posts: 72,159
Joined: 19-June 07
From: SunnySandyEggo

Hi Beth,

Welcome to UtterAccess!

In Access, it is easier to put things together (concatenate) than pull them apart (parse). So, your "unique" number can be considered to have three components: the prefix, date, and sequence number.

As George said, you probably don't need to store the prefix, if its going to be fixed anyway. For the date component, do you already have a date field to use for it? If so, then we don't even need to worry about it as well.

Then, the only important component we need to worry about would be the sequence number. If we store the sequence number separately from the date, we can easily increment it on a per date basis.

Just my 2 cents...

Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
post Jan 11 2018, 12:44 PM

Posts: 4
Joined: 11-January 18

Thank you. You are right about things that aren't supposed to change. Also if I set this up right I can use it as a template for other clients in the future.
Go to the top of the page
post Jan 12 2018, 02:19 AM

Posts: 844
Joined: 26-January 14
From: London, UK

Note that mm-dd-yyyy is a very poor format to use for dates as part of an identifier string. If you use yyyy-mm-dd instead the numbers will be allocated in a natural sequence and it will be much easier to sort and search for the number you want.
Go to the top of the page
post Jan 12 2018, 05:11 AM

UtterAccess VIP
Posts: 9,537
Joined: 10-February 04
From: South Charleston, WV

The bottom line is that there are 2 approaches: the Dmax approach and the table approach. The Dmax approach requires less programming, but has long been accused of possibly giving 2 (or more) users the same number. More recently I read that that problem has been solved (or never existed). Not sure how exactly. The dbGuy has posted Dmax code. The other way is to store the number in a table and add 1 to the number every time somebody uses it. In your case you also need a date field so you can reset the number when the date changes.

Robert Crouser

My company's website
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    25th May 2018 - 05:16 PM