Access 2007 Max Value on #'s in field with Alpha and Numeric values

[ITALIAORIANA]

Hello,

Is it possible to get the max value on numbers only in a field that has both numeric and alpha values?

I am using Access 2007 and have a field called Version - the version can be "standard" or a number. (1,2,3 etc..)

I used =MAX(B4:B27)+1 to get this to work in excel, and it works like a charm. I transferred the information to access and used =Max([version])+1. This formula only works in access as long as no text is entered in the field. Otherwise I get #Error.

I am trying to calculate the next code version available to use for a client without having to scroll through the previous to find the last number used.

For example if I have 5 clients and used 3 versions of code I want to be able to see 4 populated in the field so I will know immediately what version number to use for the next client.

Any help would be greatly appreciated.

 

[Remou]

This depends on the format of your fields, for example:

Val("123 abc")

Will return 123.

However, it seems likely that you will need a combination of Left or Right and an IIF statement.

http://lessthandot.com

 

[ITALIAORIANA]

Remou,

I have the field formatted as "text"
I got it to work the way I need it to by using the "Val(123 abc) from your suggestion.

=Max(Val([Version])+1)is what I used and it works perfectly now. It ignores the text and only gives me the max plus one on the numeric.

Thanks for you help.
Deana