Convert text to number in query

[bkanealy]

All,

I have two tables one is linked from an AS400. The first table is an extract where the field I want to search is text, but the field in the linked table is a number. I've been trying to use the "format" function in a query and then use the query to pull against the linked table, but I'm getting an error;

Type mismatch in JOIN expression.

Here is the formula I'm using to convert the text to a number.

Newfield: Format(Mid([Fieldname],8,6),"000000")

Am I close? Thanks in advance
Brian

 

[RichO]

If it's a long integer:

NewField: CLng(Mid([Fieldname],8,6))


For a short integer use CInt and use CSng or CDbl for decimal numbers.

 

[bkanealy]

If it's a long integer:

NewField: CLng(Mid([Fieldname],8,6))


For a short integer use CInt and use CSng or CDbl for decimal numbers.

Rich,

Thanks very much.

Brian