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:

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!!

What's the data type of SequenceNmbr?

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...

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.

[/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]

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

This becomes clearer (sort of). Something is still bothering me: Why do we find sequences such as:
While I would expect:

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?