dmax syntax

[John Sh]

I have a string field "BoxNo" that has a mix of numbers and text. I.e. "5" and "Bulk". I need the highest number from the field.
Without any conversion it always returns say 9 rather than 10, with "9" being a higher ansi character than "1"
I am using the dmax function but can't get the syntax right.
My basic query is "Me.txtBoxNo = DMax("val(""boxno"")", "All", "[family]")"
I have tried all the variations between Val and boxno that I can think of but always get the error "Syntax error in expression"max(val("boxno"))".
As always, any suggestions greatfully accepted.

 

[arnelgp]

[family] should be Yes/No field.

Me.txtBoxNo = DMax("Format(val('boxno'), '0000')", "All", "[family]")"

 

[The_Doc_Man]

Just note that Arnel's answer should work but only for up to 4 digits. Also, with the string being a mix of text and digits, does it ever happen that the BoxNo starts with text? Or do they ALWAYS start with digits followed by text?

 

[Pat Hartman]

Sounds like you may be using one field when you should be using two. Why are some values strings and others numbers?

 

[John Sh]

The boxNo field had entries like "Bulk" etc. There were only a few so I shifted them elsewhere in the table.
I have converted the rest to integers, they only go up to 90.
What threw me was the crazy combination of double and single quotes. My final code is below.
Ivar = Me.cboFamily
Me.txtBoxNo = DMax("boxno", "All", "[family]='" & Ivar & "'")
This works perfectly and returns the highest box number for a particular family.
My thanks to those who offered much needed help.

 

[Pat Hartman]

Strings work differently from numbers. If your strings contain 9 and 10, then 9 would be "highest". However if your field is defined as numeric, then 10 would be highest.

Strings are examined character by character, left to right and 9 is > 1 so that makes 9 higher than 10.