Access Query -FIND HIGHEST ALPHANUMERIC VALUE

[jhjarvie]

I have an access table which has a field populated with alphanumeric values i.e. AVF1, AVF2, AVF3 and so on(i.e. AVF100 will be larger than AVF99), I need to find the highest single value in that field using a query ....any help?

 

[dhookom]

You can use the expression to get the numeric value:

  SELECT Max(Val(Mid([that field],4))) As MaxVal
  FROM tblNoName;

Duane
Hook'D on Access
MS Access MVP

 

[randy700]

Include an extra column (or two) in a query, separating the field.
First new column: = left(fieldname, 3).
Second new column: = right(fieldname,len(fieldname)-3).
Sort by second new column, then by first new column.
It's not necessary to display all columns.


Randy