Data type mismatch when querying Tax ID

Darrell Burns 141 Reputation points
2021-01-26T18:02:38.297+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
830 questions
0 comments No comments
{count} votes

2 additional answers

Sort by: Most helpful
  1. Bruce H 1 Reputation point
    2021-01-26T18:30:09.173+00:00

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

    0 comments No comments

  2. Darrell Burns 141 Reputation points
    2021-01-27T12:33:42.427+00:00

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