Help needed for using max and mid functions in MS Access 1

[PhpDeveloper]

Hi,

I tried to write a query seems pretty simple, but it did not work properly. I have a text field in an Access table; its format combines letters with digits, something like this: SSID-1, SSID-12334. Each time when inserting record into this table, I need to judge the maximum digit in the right part of the string in Field2. For example I have following fields in a table named as Table1 like this:

Table1

MyID (PK) MyField
1 SSID-1
2 SSID-2
3 SSID-2
4 SSID-3
5 SSID-4
6 SSID-4
7 SSID-5
8 SSID-6
9 SSID-3
10 SSID-4
11 SSID-7
12 SSID-8
13 SSID-9
14 SSID-10

First of all, I have to say that there are must be some duplicated records in MyField. And my query is like the following:

SELECT max(Mid(MyField,6))
FROM Table1;

After I ran it many many times in Access, do you know what value it returns? It is actually 9 not 10. Surprise? I knew that it is because of the data type of MyField is set up as text not number, so is there solution for this?

Thanks a lot!

 

[drctx]

you can convert the expression to an integer like this.

Max(CInt(Mid([MyField],6)))