Sorting return values

[brumshine]

I've ran into a little dilemma when I attempt to use the MIN and MAX features from the Total: option in the design view of access. My data is and must stay stored as a string, however the string contains only numbers between 1 and 1,000,000. I'm attempting to return the correct minimum and maximum values. Currently min for a given criteria can return 150,000 and max will return 9. Anyone know a way to force if to sort a string as if it were an int?

Thanks in advance,

BRUMSHINE

 

[brumshine]

I just solved this issue myself! By typing "Int(MILEAGE)" in the field it sorted correctly. Hopefully someone else will find this useful. Thanks!

BRUMSHINE

 

[MSAccessRookie]

I've ran into a little dilemma when I attempt to use the MIN and MAX features from the Total: option in the design view of access. My data is and must stay stored as a string, however the string contains only numbers between 1 and 1,000,000. I'm attempting to return the correct minimum and maximum values. Currently min for a given criteria can return 150,000 and max will return 9. Anyone know a way to force if to sort a string as if it were an int?

Thanks in advance,

BRUMSHINE

Just because your data is stored in String Format and must stay that way, does not mean that Access must also be forced to use it that way. Try inserting cInt() around the appropriate column names. That way, even though you have String data, Access will process it as Numeric data, and the numbers should sort properly. If you need to store the result for some reason, then cStr() will convert the number back to a string