How to select a range in SQL (access) that consists of 2 letters and numbers (upto 256)

Question

I have a column in my table called 'regnr' the values are two letters (if it reaches zz it will add a letter and start at aaa) followed by numbers 1-256.

I have to find a range in this column lets say "ba50" to "bc28"

I tried using WHERE 'regnr' >= "ba50" AND 'regnr <= "bc25"

This excludes numbers starting with 1 to 4 in the "ba" range so 100+ isn't showing up. When it gets to 'bb it is showing everything and once it gets to 'bc' it only shows a portion of what it should show. bc only shows "BC10", "BC11" & "BC2" even though there are registration numbers BC1 to BC25 in the database.

Answer 1

If you use your MS Access database as the front-end, meaning you do not connect to it from another application, you can use a User Defined Funtion to solve this problem.

You define such a function in a module in the MS Access environment.

I would suggest to create a function that stretches the regnr values to a fixed-length format, where the letters occupy the first 4 characters and the digits the last 8, to arrive at a string of 12 characters that is comparable via the standard inequality operators. So for instance, if you would give that function a value of "bc25" it would return "aabc00000025"

Here is the function that does just that:

Public Function MakeLong(ByVal regno As String) As String
    Dim letters As String

    ' Extract the letters from regno
    Do While regno >= "a"
        letters = letters & Left(regno, 1)
        regno = Mid(regno, 2)
    Loop
    ' Left-pad letters with "a" to 4 characters, and
    ' left-pad number with "0" to 8 digits, and concatenate:
    MakeLong = Right("aaaa" & letters, 4) & Right("0000000" & regno, 8)
End Function

Then in your query you can write:

WHERE MakeLong('' & regnr) BETWEEN MakeLong("ba50") AND MakeLong("bc25")

If you expect the letter part to occupy more than 4 characters, or the digit part more than 8, you can adjust this function accordingly to return a longer string.

Note that with this use of UDFs you will not benefit from any indexes on your table, so if you have many records, the performance might become really bad.

If this becomes an issue, I would suggest to use a different coding format for your regnr column, if possible.