Combo Box source

[Guest]

Hello.
I have a combo box with a query as row source. Now i need to add another
table to the query, but the problem is that the field that joins the 2 tables
have different data types. It is a Text one a table and Number on the other.
Both tables are linked tables from databases that i don't administer. Is it
possible to convert one of the fields on the join query ?

Thanks

Luis

 

[Ken Snell \(MVP\)]

I assume that the two fields that would be joined are of the different data
types.

You can use what is called a "non-equi"-join in your query, and cast the
numeric field into a text format, for example:

SELECT Table1.*, Table2.*
FROM Table 1 INNER JOIN Table 2
ON Table1.TextField = CStr(Table2.NumberField);

 

[Guest]

That will work well assuming there are no leading zeros in the text version.
If there are, then the Format function would be a better choice:

SELECT Table1.*, Table2.*
FROM Table 1 INNER JOIN Table 2
ON Table1.TextField = Format(Table2.NumberField,"000000");

You would want to use a number of zeros equal to the length of the text field.