MAX function is not working in Access 2007 DB

[falen_boyz]

Hello, I was using a software using MS Access 2007 as database. It was working fine till morning when I got problem with MAX function. I was using a serial number to record transactions but today when I tried to enter transaction the MAX function was not giving maximum value in database. Actually I had reached to 9999 serial and when I created 10000 entry it stopped working. Now every time I try it comes up with 9999 as maximum value instead of 10000. What could be problem. I am using this query to perform function.

SQL Query= select MAX(variable) from table where variable like 'I%'.

[pbaldy]

One possibility is that the data type of your field is text. That would cause an alphabetic sort, which would make 9999 greater than 10000.

[falen_boyz]

One possibility is that the data type of your field is text. That would cause an alphabetic sort, which would make 9999 greater than 10000.

Yes, you are right. It is a text field and I can't find any solution. Any solution in your mind?

[pbaldy]

Either change the field to numeric or use CLng() in your SQL. I'd do the first since the field is treated as a number.

[falen_boyz]

Either change the field to numeric or use CLng() in your SQL. I'd do the first since the field is treated as a number.

I can't change field to numeric as I am saving serial numbers as SI0000. It did not cause any problems when 999 and 1000 serial numbers were inserted.

[pbaldy]

Well, you're seeing the inherent conflict of saving a value as text but wanting it to act like a number. I would store the numeric part separately from the text part and put them together in forms/reports. You can also try this in your SQL

Max(CLng(Mid(FieldName, 2)))