Prev: 'asctrls.ocx' error
Next: Combo Boxes
From: inspirz on 8 Mar 2010 17:01
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!
From: arlene ramirez on 8 Mar 2010 17:33


"inspirz" wrote:subed going to jail

> 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!
From: John W. Vinson on 8 Mar 2010 18:40
On Mon, 8 Mar 2010 14:01:01 -0800, inspirz <inspirz(a)discussions.microsoft.com>
wrote:

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

--

John W. Vinson [MVP]
From: Hans Up on 8 Mar 2010 18:49
inspirz wrote:
>
> 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.
From: De Jager on 13 Mar 2010 12:36

"inspirz" <inspirz(a)discussions.microsoft.com> wrote in message
news:4E2BCB1D-0662-44CB-BE79-D3AE57BB0EF5(a)microsoft.com...
> 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!

 |  Next  |  Last
Pages: 1 2
Prev: 'asctrls.ocx' error
Next: Combo Boxes