I have an Access database with one table that contains a number of fields. I use the database with Visual Basic 6.0. One of the fields (CustNumber) contains a sequence that I need to show up in a specific sorted order, but a simple "Order By" doesn't work for me. The data in that field consists of one of two combinations. It will be either a "N" + a "number" OR a "N" + a "number" & a "B" + a "number"; such as N14 OR N3B12.

The first example is what I have, the second example is what I get after my Select statement and the third example is what I need to really end up with. Any help would be appreciated with the 'right' Order By.

My Select statement is:
Select * From TABLE Order By CustNumber

Example 1. Starting data ----->
CustNumber
  N1
  N21B7
  N4B1
  N31B81
  N4B5
  N4
  N21
  N31
  N31B6
  N50

Example 2. After the "Order By"
CustNumber
  N1
  N4
  N21
  N31
  N50
  N4B1
  N4B5
  N21B7
  N31B6
  N31B81

Example 3. What I really need!
CustNumber
  N1
  N4
  N4B1
  N4B5
  N21
  N21B7
  N31
  N31B6
  N31B81
  N50

may be you should create one more column and store your order (it could be a number like 1,2..etc.)in that column and simply use that column for order by..

You could probably split up the customer number into 2 extra calculated fields in your query and then sort by those 2 fields. I'm not a big user of Access so not sure what string manipulation functions are available, but its going to be pretty messy.

If these numbers which are embedded in your customer number have some meaning on their own then you should've stored them separately in the first place, then you wouldn't have this mess. That's 1st normal form!

I have to stick with what I have because there is a lot of other code in my program that looks for this sequence. I just need a way to "sort" it in the database.