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

 

[Pat Hartman]

You really should fix the table definition. You seemed to have mushed text and numbers in your Access table. Split the field into two so that you can do simple joins on the numeric fields. You can always concatenate the text and numbers for display if you wan to.