Sorting Text as a number in Access

<<but it omits the operations that contain text values such as 2A. >>

 It should not.  Val("2A") will return 2.

Jim.

If all values start with the numeric part, then as Jim said, Val() will solve your problem.  If the value has embedded letters or special characters, then that requires a code solution.  I created a custom solution for a construction company because their drawing numbers were built in three parts and each part might have letters and numbers.  Essentially, it involves pulling out all the numeric parts and fixing them in width by right justifying with leading zeros.  So 2 becomes 0002 and 10 becomes 0010 and that makes 2 sort before 10.  I didn't post the code I wrote because it wouldn't help you unless your codes followed the same rules as those for the Drawing Log app I built.

Pat that is the issue that I am currently having. The text values need to be included because they help to distinguish between different machines. For example operation 2 might be Saw-1 and operation 2A might be Lathe-3. I am trying to figure out how to write a code that will pad my numbers so that they will sort as you stated above. Any suggestions on how to go about doing that?

As long as the number is in the front, then val() will work fine.

  But you can't have "2A3" and get 23 with val().  It will always return 2.

Jim.

I didn't say to ignore the text values.  I said you have to standardize the numeric values so they are all the same length.  If the text parts can be variable length, they will also cause sorting issues so they will also need to be right justified.  You have to create a field on which you sort.  It doesn't ever have to be displayed.  It just needs to be used for sorting.  Your code needs to construct something like:

0002???A
0010??AB

I used ? as the filler for the text because you can't "see" spaces and it really doesn't matter what character you use.  The point is you have to RIGHT-JUSTIFY text strings to sort them.

So although you can use Val() to extract the leading numbers, that doesn't really help with the letters.  My code looped through the fields character by character to build the sort string.