text conversion to number on select query

[inspirz]

Hi,

In a Select Query I'm joining 2 tables by Item ID (unique value, similar to
Social Security Number) but 1 table created by IT has Item ID as a "number"
value and the other table has it as a "text" value.

How can I in a Select Query, create a formula that can either have the text
as a number value and vice versa so i can link the 2 without getting "type
mismatch in expression."

I think I can use Cdbl Value or something like that in the formula but not
sure.

Thanks!

 

[arlene ramirez]

"inspirz" wrote:subed going to jail

 

[John W. Vinson]

Hi,

In a Select Query I'm joining 2 tables by Item ID (unique value, similar to
Social Security Number) but 1 table created by IT has Item ID as a "number"
value and the other table has it as a "text" value.

How can I in a Select Query, create a formula that can either have the text
as a number value and vice versa so i can link the 2 without getting "type
mismatch in expression."

I think I can use Cdbl Value or something like that in the formula but not
sure.

Thanks!

I wouldn't recommend CDbl: Double Float numbers might give you roundoff
problems. If the ID is less than 2147483647 you can use CLng; or you can use
CStr() on the number field and join on that instead.

 

[Hans Up]

In a Select Query I'm joining 2 tables by Item ID (unique value, similar to
Social Security Number) but 1 table created by IT has Item ID as a "number"
value and the other table has it as a "text" value.

How can I in a Select Query, create a formula that can either have the text
as a number value and vice versa so i can link the 2 without getting "type
mismatch in expression."

In this example, item_id is autonumber in Table1 and text data type in
Table2.

SELECT *
FROM
Table1 INNER JOIN Table2
ON Table1.item_id = CLng(Table2.item_id);

The CLng function casts the text item_id as a Long data type, which
matches with the autonumber in the other table.

 

[De Jager]

Hi,

In a Select Query I'm joining 2 tables by Item ID (unique value, similar
to
Social Security Number) but 1 table created by IT has Item ID as a
"number"
value and the other table has it as a "text" value.

How can I in a Select Query, create a formula that can either have the
text
as a number value and vice versa so i can link the 2 without getting "type
mismatch in expression."

I think I can use Cdbl Value or something like that in the formula but not
sure.

Thanks!

 

[joelgeraldine]

jkjkjk