Split alphanumeric field

[aswin34]

Hi
I need to split alphanumeric field ( such as 567E, 567S) into 567 and E separately. But 567 should be text type. It had relationship with another table with the connected filed is of text type.

I used val(Field) which gives numeric separately (567 with number data type). When I link this another table where 567 field is defined as text, which I cannot modify, getting error message "Data Type mismatch"

Appreciate if somebody helps on this

Thanks

[ranman256]

in your query have
[code], val([code]) as CodeNum, , cStr(val([code])) as CodeStr

to get the code, its num value, and its string value. Now you can join to whichever you need.

[JoeM]

One way is to use the FORMAT function to coerce it back to text, i.e.

Format(Val([MyField]),"0")

[aswin34]

Hi ranman256 & JoeM

It works fine. Thanks for your quick response and solution