question

DarrellBurns-1376 avatar image
0 Votes"
DarrellBurns-1376 asked DarrellBurns-1376 answered

Data type mismatch when querying Tax ID

I've created a simple query to look up customers by Tax ID. Since the users don't always insert the hyphens in the right place, I strip out the hyphens using the REPLACE function. But the query throws a Data Type Mismatch error (see example). I've tried converting the stripped TIN with CSTR and CDBL, but to no avail.
Anybody have any ideas?60711-access-bug.png


office-access-dev
access-bug.png (94.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrellBurns-1376 avatar image
0 Votes"
DarrellBurns-1376 answered

Ah, I figured it out...
62178-tin-search-solution.png



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

HulseyBruceB-2106 avatar image
0 Votes"
HulseyBruceB-2106 answered

What is the datatype of TIN in your table? If it is a long integer, try Clng(Replace(...))
-Bruce

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DarrellBurns-1376 avatar image
0 Votes"
DarrellBurns-1376 answered HulseyBruceB-2106 edited

It's a string. It couldn't be numeric with hyphens in it.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Oh, I see now....it looks like Owner_TIN has nulls for one or more records.

If you were not storing hyphens in Owner_TIN you could search them using
select * from Owner where Owner_TIN = replace([Search for TIN],"-","")
regardless of the null values.

Since you are storing hyphens which you need to get rid of first and you have nulls in Owner_TIN you would need to use

select * from Owner where replace(nz(Owner_TIN,""),"-","") = replace([Search for TIN],"-","")

or possibly
select * from Owner where Owner_TIN is not null and replace(Owner_TIN,"-","") = replace([Search for TIN],"-","")

-Bruce

0 Votes 0 ·