text conversion to number on select query

I

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!
 
J

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!
Click to expand...

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.
 
H

Hans Up

inspirz said:
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."
Click to expand...

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.
 
D

De Jager

inspirz said:
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!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro to select and delete levels of a document 2
Select Text to End of Footnote 9
Autonumber field in Excel that behaves the same as Access 0
Creating a Chart on form from selected data in query. 0
Auto populate value from one task to another in MS PROJECT 0
Visio 2010 - Organization Chart Wizard - Hebrew Excel Database 0
Creating a Search using a combo box and then creating a report. 4
Finding the proper style field and the numbered paragraph value within the style 0

Top