Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: DMax to Create Sequence Numbers not working!!

    Hi,

    I have an access table that has many fields, but the three I'm concerned with are:

    Field 1 - Program Number (Field 2 + Field 3 combined)
    Field 2 - Load Center (3 digit number)
    Field 3 - Sequence Number

    The following is how the new sequence number is created:

    Code:
    ExistingSequenceNmbr = DMax("SequenceNmbr", "ProgramInfoTbl", "LoadCenter=" & Me.cbo_LoadCenter & "")
    NewSequenceNmbr = ExistingSequenceNmbr + 1
    My issue is with the sequence number fields. For certain load centers, there are sequence numbers such as
    0999
    1000
    1781

    For this, if there is a 0 in front oft he number, this is what is taken as the maximum sequence number. If there is no zero (so it'd just be 999) then it uses the 1000 as the maximum number. The issue is that it never actually grabs the highest number in there... can anyone help me try to figure out why this is not working?? I would greatly appreciate it as I have no idea what the issue is... Thank you!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the data type of SequenceNmbr?
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    It was set as text... but I realize that this is why it was not working.

    The reason I had it as text is because there are programs stored in there from 10 years ago before this system was used that have letters in it... i.e. 1510999A or 1510999A01. Therefore, I had set the data type to text so that it would allow these values to be stored.

    Is there a way to keep it as text and have it ignore these values when it chooses a sequence number?

    Alternatively, I could create a new table just for those program numbers that have letters in it. However, when I do a search, would there be a way for Access to search both the table that has just numerical numbers and the table that has letters in it and display the results on the same page? All of the fields would be the exact same in both tables...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would try to create a query based on the table "ProgramInfoTbl" with an ORDER BY clause on "SequenceNmbr", use it in the DMAX() expression (or use a SELECT TOP 1?) and process the result according to some rules that remain to be defined. It's hard to be precise without knowing the data set.
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    [/QUOTE]Here is an example data set:

    Program # Load Center Sequence
    24211462 242 11462
    24211463 242 11463
    24211463A01 242 11463A01
    24211463R 242 11463R
    24211464 242 11464
    24211465 242 11465
    24211466 242 11466
    24211467 242 11467
    24211468 242 11468
    24211468A1 242 11468A1
    24211469 242 11469
    24211470 242 11470
    24211470S 242 11470S
    24211470A 242 11470A
    24211470R02 242 11470R02
    24211471 242 11471
    24211472 242 11472[/QUOTE]

  6. #6
    Join Date
    Nov 2011
    Posts
    57
    That did not come out like it did when I typed it... the load center for all of them is the middle number (242) then the program # is the load center + sequence number

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This becomes clearer (sort of). Something is still bothering me: Why do we find sequences such as:
    Code:
    24211462	 242	 11462
    24211463	 242	 11463
    24211463A01      242	 11463A01
    24211463R	 242	 11463R
    24211464	 242	 11464
    While I would expect:
    Code:
    24211462	 242	 11462
    24211463	 242	 11463
    24211464A01      242	 11464A01
    24211465R	 242	 11465R
    24211466	 242	 11466
    Have a nice day!

  8. #8
    Join Date
    Nov 2011
    Posts
    57
    It is because these were created in 1999, where the process was not automated but manual. A smart numbering system was used where a sequence number would correspond to a specific part, and then the letters were used in case that multiple programs were needed to make the same part.

    I think I am just going to put these letters in a different table. It might not be pretty, but it seems like the easiest thing seeing that my table now works fine with just the numbers.

    I have another quick question... I have created a user form to allow for more new programs to be added to the table mentioned here. I also have a separate form to used to search through this table and bring up any programs. You can search by either the program number or various other fields. I noticed that when I add a program using the form, and then try to search for it using the search form, it does not return any results. However, when I open up the table it is there and has been saved. Do you know what the reason for this may be and if there's a way to fix this?

Posting Permissions

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