Is there a way around a Type Mismatch Error?

  • Thread starter Thread starter Kristen
  • Start date Start date
K

Kristen

Hello,

I'm getting a type mismatch error on my query. I'm using
2 tables that are from 2 different MS Seq. databases that
I've linked in via ODBC. The fields I really need to link
on are of different type (number and text). Since I
cannot change the types, is there a way to write an SQL
statement that converts wichever to match the other?

I've tried placing either CINT and CDBL in front of the
text (field name) and nothing is working. Is there
a "ToNumer" or "ToText" function that I can use?

Any assistance offered is VERY much appreciated!

Thanks
 
Kristen,

I have the exact same problem in that the two tables I
want to query have the same information but the fields are
formattted in different ways (one as text, one as
number). I wrote a really ugly bit of VB as a work
around, but I'd love to find a better way. Has anyone
repsonded?

Thanks,

Mark
 
Hi Kristen,

You may want to take a look at a June 4 post in the
queries newsgroup by Racer57. Following is an excerpt
from a response from Rick Brandt, who said it more
concisely than I.
----------------------------------------------------------
You can have a join like...

SELECT blah blah
FROM Table1 INNER JOIN Table2
ON CInt(Table1.TextField) = Table2.IntegerField

....you just can't build it in the query design grid. You
have to do it
while in SQL view.
----------------------------------------------------------

But, of course, you would want to replace CInt with one
of the other conversion types (such as CLng())if your
number field is not an integer field. You can do the
entire query in the builder if you like, but then just go
to SQL view to modify the ON statement.

Post back if you aren't able to get this to work.

-Ted
 
Thanks for the reponse. It works (oddly if I use CStr()
on the number, but NOT if I use CInt() on the text), but
it takes an UNBELIEVABLY long time to run. Like 60 or 70
minutes to retrieve 6 or 8 entries. However, at least I'm
headed in the right direction.

Thanks again,

Mark
 
CInt on the text could fail for several reasons.

One: the text number is too big to be converted to an integer.
Two: the text contains non-numeric characters O (oh) vice 0 (zero)
Three: the text is a zero-length string or is null

CStr is a bit more tolerant, although it will fail if the field contains null.

And it will take quite a while, since you probably lose the ability to use indexes.
 
Back
Top