Query: Connecting string and numeric fields

[foxrainer]

Hi,

I need to build a query in which two tables are connected.

However,
one table uses the ID field as a text, the other as a numeric expression.

I am not able to change the field expressions, as both tables are used in seperate programs, but DO use the same numbers ( i.e., an ID may be 20002 in the text_ID field as well as the numeric_ID field).

In other programs I could use the: Str(numeric_field) = string_Field, but I can't find that in access.

Also, it would help if this could be done directily with the expression builder.

Thank you in advance

Rainer

 

[SkipVought]

Hi,

1. Do you have any IDs that have leading zeros? So if your ID is 8 characters in length...

Where A.ID = Right("0000000"&B.ID, 8)

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[foxrainer]

Skip,

no leading Zero's in either the numeric nor string ID.

But, as mentioned, one field is Numeric, the other Text! In order to relate the two, one would need to be converted.

Somehow, it should work to do the following:
WHERE VAL(textID)=StringID

However, I tried that and it didn't work.

 

[traingamer]

I got this to work to link an autonumber and text id:

SELECT Staff.Staff_name, Staff2.Name
FROM Staff INNER JOIN Staff2 ON Staff.staffid = int(Staff2.Id);

traingamer

 

[foxrainer]

traingamer,

thank you VERY much,

Rainer