Type mismatch problem

[Guest]

Hi,

I am a little new to Access, so bear with me. I am trying to write a query
in which I pull data from two tables, but each is sourced from a different
data source. This will be an ongoing query so I am linking to the tables
instead of importing them. The problem I have is:
I need to link the tables on two fields. One is a date field, the other is
numeric. On my SQL 2000 table this holds true. But on my AS400 (DB2) table,
the fields are both coming accross as text. Is there a way (possibly in an
SQL statement) to link these fields? Can I convert the SQL fields to text?
Obviously if I try to do this in design view, it tells me I can't modify the
fields.

Thanks...

 

[Brendan Reynolds]

Here's an example ...

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2 ON Format$(Table1.TestDate, "dd/mm/yyyy") =
Table2.TestText;

Access can't display this query in design view, you'll need to edit it in
SQL view.

 

[John Spencer]

You could possibly join the tables with an inner join but I think that you
will find that the result is not updatable. You can only do this type of
join in the SQL window.

SELECT *
FROM SQLTable INNER JOIN AS400Table
ON Format(SQLTable.DateField,"yyyymmdd") = AS400Table.TextField
AND CStr(SQLTable.NumberField) = AS400Table.TextField

You will have to design the date format to comply with the AS400 date
format. Also, if the Number field is not a long or integer field you may
have problems with the decimals. In that case, you could use a format to
force agreement.

Another option, but with the same problem is to build a query based on one
of the tables that returns the values converted to text fields and then use
that query to join to the remaining table.

 

[Guest]

That worked.

Thanks!!
--
John

You could possibly join the tables with an inner join but I think that you
will find that the result is not updatable. You can only do this type of
join in the SQL window.

SELECT *
FROM SQLTable INNER JOIN AS400Table
ON Format(SQLTable.DateField,"yyyymmdd") = AS400Table.TextField
AND CStr(SQLTable.NumberField) = AS400Table.TextField

You will have to design the date format to comply with the AS400 date
format. Also, if the Number field is not a long or integer field you may
have problems with the decimals. In that case, you could use a format to
force agreement.

Another option, but with the same problem is to build a query based on one
of the tables that returns the values converted to text fields and then use
that query to join to the remaining table.