#  Other Applications & Softwares  > Access Tables & Databases >  >  Is their any Query to find the highest value in a field

## L_ter

Hi,

I have a table with the primary key field populated with a text value AVF1, AVF2, AVF3 and so on, when I run a query to find the highest value it always returns AVF99 however I know there are higher values i.e. AVF200
how can I find the highest value in this field?

----------


## bhill

Your field is text, so from a alpha standpoint, AVF99 is the highest. You can however isolate just the numeric part of the data, then sort on that. This formula will work in your query as long as AVF is always the prefix - it just needs to be three characters.




```
Please Login or Register  to view this content.
```


Broken down, the Mid statement will isolate the value in your [fieldname] from the 4th character (skipping the first 3 - AVF), and going 20 characters out. 20 is an arbitrary number - it could be 10 or 200 - I assumed 20 would fit the bill. What is isolated is still text at that point, so Cint() will convert it to an integer. You can then sort numerically on that field to acheive the porper order you desire or find the max/min values. Hope this helps.

Brent

----------


## L_ter

Thanks for the solution bhill but if AVF200 exists in the table,will it even then shows AVF99.

I just only want that if there are 1-200 values in any table so how can i find out the highest value i.e 200.
Query to  display the highest value in the table.
Thanks once again

----------


## bhill

If you surround the whole phrase with the MAX statement, it will pull the greatest numerical value when you query it. I used this on a table with AVF001, AVF002, AVF099, and AVF200 and it pulled AVF200.




```
Please Login or Register  to view this content.
```

----------

