determine if text or numeric value using Iif

[melika]

Hi,

I have a field that has data in the format of ##LLLNLL. ex) 08NOV7NY

I call on that field in a query and try to pull out the last two letters for the location. So I use the code, Location: Right(

, 2)  and that seems to work.  However sometimes the user just puts the date in 11/08/2007, and I have no control over that.  So, my statment returns the value 07. 

How can I modify the statment so that it knows when it is a text vs a number and to only keep the text.  I had something like...

Location: IIf(Right([Code], 2) = Number, "Unknown", Right([Code], 2))

But I don't think the Number part is right.

Thanks!

 

[KenHigg]

IsNumeric() and IsDate()



ken

 

[melika]

Thanks!

It seems so obvious once you've told me!

 

[KenHigg]

Glad to help -

ken