Help with access query

[sarang_k]

Hi all, I am having a table in which one field data type is text and the data stored in it is characters + numbers for example SD01TU9,SD01TU8,SD01TU10. Now the problem is i want the max from it.As in the above example the max should show SD01TU10 but when i use max function on that field it shows SD01TU9. Please any one can solve it. Thanks in advance.

[Mycroft Holmes]

You have a number of choices, you have a requirement to do numerical operations on a field with mixed data. Create another field and extract the numeric component from the mixed field Use a view (query in Access I think) to do the same thing just don't store it Go back to your initial design that put the mixed data in there and address the problem at the source. As your data seems to be consistent (SD and TU) you could do this with nested replace statements. It gets a little tricky if your text data changes from the standard, then use regex.

Never underestimate the power of human stupidity RAH

[_Damian S_]

If all the strings include the letters "TU" before the numbers at the end, you could use a combination of instr() and len() and right() to get what you need... I'm doing this from memory and without access to Access to test, but it would be something like this:

clng(right(FIELDNAME, len(FIELDNAME)-instr(FIELDNAME, "TU")))

Which basically says locate the position of TU in the field, then take the characters to the right of them, convert to long and then you can happily find the max of those numbers.

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! If you need a laugh, check out my Vodafone World of Difference application | If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

[_Damian S_]

Are you stalking me? :laugh:

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!! If you need a laugh, check out my Vodafone World of Difference application | If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

[riced]

If you have the option, you might consider ensuring that the data all have the same number of digits at end. E.g. use SD01TU09 instead of SD01TU9, or SD01TU009 if there can be three digits. This would mean you don't have to monkey around with substrings.

Regards David R --------------------------------------------------------------- "Every program eventually becomes rococo, and then rubble." - Alan Perlis