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?
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.
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.
Please Login or Register to view this content.
Brent
Last edited by bhill; 03-18-2009 at 11:31 AM.
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
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks