Results 1 to 14 of 14
  1. #1
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110

    Sequencing number issue

    Hi All,

    i’ve a problem on the sequencing number. Hope your can help me out of this.



    Code:
    Private Sub Frame97_AfterUpdate()
    Dim LValue As String
     
    LValue = Format(Date, “yymm”)
     
    Me.txtscar = Nz(DMax(“SCAR”, ”F1NCtbl”, “SCAR = “”IQA ” & [LValue]), 0) + 1
     
    End Sub
    What i wish to get is
    IQA 2002XX (xx – running number)

    In my table, i have a field for SCAR only. Not sure why i cant get the number. Can you help on this?

    Thank You,
    Regards,
    tonyshieh

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not sure what "i have a field for SCAR only" means. Does the field contain the whole value, like "IQA 20021"? I'd store the components separately, which would make your job easier. If you're storing it all together, I suspect you'd need to peel off the bit after the 2002 since it could be one or more digits and add 1 to that. Adding 1 to a text value could get problematic. Your criteria would need to check the left 8 characters as well, since you'll never get match on "IQA 2002" if the actual values contain the running number.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by pbaldy View Post
    I'm not sure what "i have a field for SCAR only" means. Does the field contain the whole value, like "IQA 20021"? I'd store the components separately, which would make your job easier. If you're storing it all together, I suspect you'd need to peel off the bit after the 2002 since it could be one or more digits and add 1 to that. Adding 1 to a text value could get problematic. Your criteria would need to check the left 8 characters as well, since you'll never get match on "IQA 2002" if the actual values contain the running number.
    Yup, my SCAR field is going to contain IQA 200201.
    Your suggestion is to seperate IQA and 200201?
    Cause 2002 is actually YYMM and follow by running number.
    Every month i need to have a new number. For example.
    Feb - 200201 ......
    Mar - 200301 ... every month start with new number.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    On a mobile right now, but I would separate IQA, 2002, and the running number. If the IQA never changes, I wouldn't even save it, I'd just concatenate it on forms and reports. Either way, you have a much simpler DMax.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Is this exactly how your code appears the VBE?

    SCAR, F1NCtbl, SCAR = “”IQA & [LValue]), 0) + 1

    If so it appears to have been created somewhere and inserted into your code...and IIRC...the Access Gnomes don't like this type of format! Try replacing it with

    "SCAR", "F1NCtbl", "SCAR = ""IQA " & [LValue]), 0) + 1

    and see what happens.

    (Late note...also not sure about the last part

    "SCAR = ""IQA " & [LValue]), 0) + 1

    doesn't seem quite right.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Another factor for consideration - Access and the other relational databases work very well when organized with 1 fact in 1 field. Also described as "atomic values". You can store data following relational database principles, and, then concatenate fields into some arrangement that makes sense in your environment. See the attached file for more.

    Good luck with your project.
    Attached Files Attached Files

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Linq's changes won't work, as mentioned you'd never get a match even if the syntax were fixed (which it still hasn't been).

    Orange is reinforcing what I mentioned previously.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Hi Moderator (Orange / Paul)

    Thanks for your advise. As i'm not an expert, i may need your more advise.
    I understand that "SQA" should be separate out from YYMM and running number.
    Can both of you teach me how to show up the 1st number even though table is empty?

    Me.txtscar = Nz(DMax(“SCAR”, ”F1NCtbl”), 0) + 1 <<< how to make YYMM show up in me.txtscar?



    Code:
    Private Sub Frame97_AfterUpdate()
    
    Dim LValue As String
    
    
    LValue = Format(Date, "yymm")
    
    
    Me.txtscar = Nz(DMax(“SCAR”, ”F1NCtbl”), 0) + 1
    
    
    
    
    End Sub

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you store them separately:

    Me.txtscar = Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110
    Quote Originally Posted by pbaldy View Post
    If you store them separately:

    Me.txtscar = Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1
    Hi Paul,

    If i separate it out it works find. Thanks for your advise!
    One last step, would it be possible to change the sequence to two digit instead of one?
    For example - 01 , 02 ...... 09 ....
    Now i getting ... 1 , 2 ..... 9 ... single digit. I've checked in table but i'm not able to get it.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by onlylonely View Post
    One last step, would it be possible to change the sequence to two digit instead of one?
    Two ways.
    Code:
    Format(Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1,"00")
    or
    RIGHT("00" & Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1, 2)

  12. #12
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I would leave it alone in the table and use the Format() function when you pull the fields together for the user.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    onlylonely is offline Competent Performer
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    110

    Thumbs up

    Quote Originally Posted by ArviLaanemets View Post
    Two ways.
    Code:
    Format(Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1,"00")
    or
    RIGHT("00" & Nz(DMax("RunningNumberField", "F1NCtbl", "YYMMField = '" & LValue & "'"), 0) + 1, 2)
    Thanks everyone here who taught me the coding.
    Thanks Paul, orange and ArviLaanemets!

  14. #14
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What is the data type of the running number field? As noted, it needs to remain a numeric field, not text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 11-14-2015, 03:23 AM
  2. Number format issue
    By stafjoy in forum Queries
    Replies: 5
    Last Post: 07-19-2012, 08:38 AM
  3. Table auto number issue when getting to 10000
    By shabbaranks in forum Access
    Replies: 11
    Last Post: 05-18-2012, 10:14 AM
  4. Limiting textbox to number issue
    By GraemeG in forum Programming
    Replies: 3
    Last Post: 03-29-2011, 07:25 AM
  5. Replies: 38
    Last Post: 05-21-2010, 11:56 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