Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Using alphabets, numbers and date to make a serial number

    I have an access problem regarding input mask and format.
    I would want to have a serial number to be looking something like this :
    "AMP-12-06" where "AMP" is an alphabet , "12" is a number and "06" is the year. How do I go about doing it?

    I've tried a couple of format to be inserted in. Some of which are " AMP-"00"-"0\6" or "AMP-"00"- ( dateFormat="yy",[reference number]). None of them are successful. So yeah. Any help please? I need help urgently. Thank you.

  2. #2
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Do you want users determining what the serial number ought to be and then keying it, or do you want the form to figure out what the right serial number should be and autopopulating it?

  3. #3
    Join Date
    Feb 2006
    Posts
    3
    I want the form to be on autonumber. Lukily you mention it. By the way what is the difference?

  4. #4
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    Is it always:

    AMP-##-yy

    where ## = the ordinal within the year and yy = last 2 digits of the year?

    Patrick

  5. #5
    Join Date
    Feb 2006
    Posts
    3
    Yeah that's right. I've tried that format too. The one which you suggested - "AMP-"##"-"yy" but it seems to me that the ordinal is working as I wanted it to but the yy which is the year is not.

    Any other suggestions to solve the small part on the year?

  6. #6
    Join Date
    Dec 2005
    Location
    Wilmington, DE - USA
    Posts
    275
    There may be a better way to do this, but amazingly, it works.

    Assuming you are making your entries in a form, use this formula as the default value for your serial number textbox:

    ="AMP-" & Format(Mid(DMax("[SerialNum]","[izyan]","Right([SerialNum],2)='" & Format(Now(),"yy") & "'"),5,2)+1,"00") & "-" & Format(Now(),"yy")

    Update SerialNum and izyan to be your field name and table name, respectively.

    What the formula is doing is finding the serial value with the highest ordinal for the current year, and adding one to it.

    BTW, that will not work if you try to assign it as the default value of the field in your table: while Access does not mind using domain aggregate functions (DMax, DCount, DSum, DLookup) in textbox default values, they are not allowed in table default values.

    Patrick

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Change Date Into Number
    By greenbag in forum Queries
    Replies: 0
    Last Post: 11-14-2008, 04:43 PM
  2. organizing serial numbers and quantities
    By Diomeneus in forum Access
    Replies: 0
    Last Post: 11-14-2008, 03:17 PM
  3. How do I find the serial value of Now()?
    By JoeG in forum Queries
    Replies: 0
    Last Post: 03-13-2007, 08:16 AM
  4. date/time number
    By playfuljade in forum Forms
    Replies: 4
    Last Post: 12-19-2005, 12:52 PM
  5. Replies: 1
    Last Post: 12-09-2005, 10:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums