Results 1 to 7 of 7
  1. #1
    beewerks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Location
    Longmont, Colorado
    Posts
    7

    DMax +1 a field with alpha and numeric characters

    Hi everyone,

    I've searched around for a while trying to find a solution to this, but came up empty.

    I have a serial number that I am wanting to generate in the format of 18BA100, 18BA101, 18BA102, etc.

    This serial is stored in SerialTable under column YearMonth, which is formatted as short text

    When I run Dmax on that table column to increase the serial number by 1, I get a type mismatch error. I think it's because I have alpha characters in the serial number since I've used this code on other databases with just numeric values and it worked great. What do you experts think would be a solution to this, if any? Any thoughts greatly appreciated!



    My code:
    Code:
    Private Sub cmdRequery_Click()     'pull next sequential serial number
        If Nz(Me.[txtYearMonth], 0) = 0 Then
           Me.[txtYearMonth] = Nz(DMax("[YearMonth]", "[SerialTable]"), 0) + 1
        End If
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to remove the text to increment, try

    Me.[txtYearMonth] = "18BA" & Nz(DMax("right([YearMonth],3)", "[SerialTable]"), 0) + 1

  3. #3
    beewerks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Location
    Longmont, Colorado
    Posts
    7
    Quote Originally Posted by Ajax View Post
    you need to remove the text to increment, try

    Me.[txtYearMonth] = "18BA" & Nz(DMax("right([YearMonth],3)", "[SerialTable]"), 0) + 1
    Freaking genius man! I haven't found anything like that anywhere even after reading through some Access books. Much appreciated. It works great.

  4. #4
    beewerks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Location
    Longmont, Colorado
    Posts
    7
    Ok, one more question with this same type of function.

    Using the above working code from Ajax works great for querying the table, but placing that same code into an INSERT INTO command from a save button causes an argument not optional error. I'm not sure what it is missing.

    Also of note, I'm looping the code so I can create batches of serial numbers.

    Thoughts? Ideas?

    Code:
    Private Sub cmdSave_Click()    'loop to save as many records as are listed in txtQuantity
        For i = 1 To txtQuantity
        'save data to table
        CurrentDb.Execute "INSERT INTO [SerialTable](GregorianWeek, PartNumber, ITIWorkOrder, Serial) " & _
                " VALUES('" & Me.txtGregorian & "','" & Me.cboPart & "','" & Me.txtWorkorder & "','" & Me.txtSerial = "18BA" & Nz(DMax("right([Serial],3)"), 0) + 1
        Next i

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If you are running the query when the form is open and the control value has been calculated, then omit the expression and just refer to the control itself as you did for the others preceding. However, you will loop with your counter but run the same sql every time because you're not changing anything in between iterations.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    beewerks is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2014
    Location
    Longmont, Colorado
    Posts
    7
    Aha! Good idea! That seems to work pretty well. I'm not sure if it's technically the clean way of doing code, but it works and that should be sufficient. Thank you!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it works properly you must be using an autonumber field as meaningful data (since it looks like each append is exactly the same). You should not.

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

Similar Threads

  1. creation alpha numeric code
    By Jen0dorf in forum Access
    Replies: 1
    Last Post: 05-09-2016, 11:52 AM
  2. Finding numeric characters
    By Always_Learning in forum Programming
    Replies: 1
    Last Post: 09-11-2014, 06:13 AM
  3. Replies: 1
    Last Post: 03-09-2014, 06:30 PM
  4. Alpha Numeric auto id?
    By arshadmgic in forum Forms
    Replies: 7
    Last Post: 09-02-2012, 03:00 AM
  5. Recordset not showing alpha-numeric entries
    By rayhawk in forum Access
    Replies: 9
    Last Post: 10-29-2010, 08:15 AM

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