DMax on a text field - way to treat numbers uniquely from text?

[DocNice]

I have a field that needs to have both numbers and text. I need to be able to look up the highest number in that field, ignoring the text. Is there a way around the limitation of DMax on text fields? If I run it as is, then I get Z as the highest value, instead of 9, and I would get 3 as higher than 21.

Is there some trick I can use like pulling only number values or creating a recordset, etc?

Thanks.

 

[sfreeman@co.mer]

Not a problem...

Table named tblNumberText with field named Field1...

SELECT
Max(tblNumberText.Field1)

AS
MaxOfField1

FROM
tblNumberText

WHERE
(((IsNumeric([Field1]))=-1));

HTH

 

[DocNice]

Oh man, you are so awesome!

By the way, I've found a way around my problem with sorting the numbers. It's temporary but should work for a few years until we need to add another digit to the field. But for future reference, is there a way to take the result of that query and sort it as you would a number field, so that 3 doesn't come after 21?

 

[sfreeman@co.mer]

OK.

SELECT
Val([Field1])

AS
Field

FROM
tblNumberText

ORDER BY
Val([Field1]);

 

[DocNice]

Thanks, I realize that these questions must seem very basic, but a lot of times I know the concept of what needs to be done and sometimes even approximately how to accomplish it, but not the details. I really appreciate the help!

 

[sfreeman@co.mer]

You are welcome.

We all started somewhere, and despite what you might think from the attitudes of a few, NONE of us knows it all.

Happy coding.