Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    I have an invoice system that utilizes a serialized inventory. Serial numbers are stored individually in an Inventory table.

    First order; when I Enter Item AO, I want 221 to automatically populate my form
    Next order; when I Enter Item AO, I want 222 to automatically populate my form

    So on and so forth, I want to deal with to oldest receipt date first, then the next available/sequential serial number within the receipt date.

    I

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    How are sequential numbers to be assigned? It is not clear to me why numbers for November would be higher than for December. Where do the numbers start, and when do they restart?

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Your entire scenario is rather confusing, but I think I understand the issue. You obviously can't use the DMax function on the Inventory table directly, as there may be higher numbers from a previous month. So you need to run a query which returns only records for the selected Item Code for the current month (determining that could be a challenge). Once you have that you should be able to apply a DMax function to return the highest existing number for that month, increment by one, and be on your way. One thing that would concern me however would be how to deal with the first record for each new month. You may have to run a preliminary test of the query to determine if any records are returned before you do the assignment. One other thing I don't understand: you indicate the numbers between 100 and 200 are used, but your example has one set of numbers from 221 to 223. Is it that the numbers can be from 100 to 299?
    Wendell

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    The sequential numbers 100 to 200 are received into inventory each quarter with a received date, the numbers are sequential within the received shipment.

    The numbers recieved in April could lower than ones received in Jan

    They are sold sequentially (lowest to highest) from the oldest receipt date.

    Inventory Table looks like this: (3 fields are Item Code, Serial Number and Receipt Date)

    AO 221 01/01/2004
    AO 222 01/01/2004
    AO 223 01/01/2004
    AO 111 04/01/2004
    AO 112 04/01/2004
    AO 113 04/01/2004

    The above is what my combo pops up for a operator to select, however customer wants the option to simply enter Item Code and have the next available sequential populate the form, in this case 221 (the next sequential number in the oldest receipt)

    The combo is set up not to display any numbers that have already been sold.

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Think I got it

    Created query (qry_InventoryNextInputDateAndTagID) with NextSerialNumber (concatenated
    with Date and Serial Number) with sort asending on separate Date then Serial Number fields.

    Form AfterUpdate looks like this:

    <pre>strNextTag = DMin("[NextSerialNumber]", "qry_InventoryNextInputDateAndTagID", _
    "strInvCode " & " = " & "'" & strTagType & "'")
    </pre>


    ' extract serial # into form, 01/01/2004 1234567890

    <pre>Me.strRefNo = Mid(strNextTag, 12, 10)
    </pre>


    Can this: "strInvCode " & " = " & "'" & strTagType & "'") be refined?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Since the first items are fixed text, you can simplify<pre>"strInvCode " & " = " & "'" & strTagType & "'"</pre>

    to<pre>"strInvCode = '" & strTagType & "'"</pre>


  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Can I Use Dmax/Dlookup To Do This? (a2k (9.0.6926) SP-3 Jet 4.0 SP-7)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •