quirky query problem

[Tony in Michigan]

Hello!

One of the very generous group contributors provided the SQL below for a
project I am working on.

I have a slight dilema.

Table 1 has many fields with many data types. If I run it as is, I get a
data mismatch in criteria error . To get around this, I have made an
alternate table 1, which only has the field I am comparing to table 2. It
now works perfectly.

My issue; This is run accross a network via a Citrix process, so there is a
system time limit.

The process to create the alternate table 1 eats up a lot of the connection
time. Now I've mitigated this somewhat, by limiting the time frame to
include on the table.

The question:
Is there a way to trick the system into dealing with the multiple data
types? Is this something as simple as too many fields?



SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Replace(Left(Table1.FieldName, 13), "-", "") = Table2.FieldName

 

[John Spencer]

If you are getting a data mismatch error then you need to determine
which field is causing this. I am GUESSING here that table2.Fieldname
is a number field. Another possibility is the table1.fieldname contains
null in one or more records.

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Val(Replace(Left(Table1.FieldName, 13), "-", "")) = Table2.FieldName

Although I wonder if you might need to use
Val(Left(Replace(Table1.FieldName, "-", ""),13))

The second version replaces all the dashes and then trims the length to
13 characters. The first trims to 13 characters and then replaces the
dashes. Those are obviously different results.

Oh and IF the field could be null then just append a zero length string
to the field like:
Val(Left(Replace(Table1.FieldName & "", "-", ""),13))

That will end up returning 0 (zero) for any null values and then Val
won't generate an error

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================