CODEWITHSUNDEEP
ms-access
ic3b3rg
ic3b3rg

Reputation: 14927

Access Replace Function

I have the following Access query:

SELECT [city]
FROM [patient]
WHERE REPLACE([patient].[city],'  ',' ') LIKE 'San D*'

I'm relatively certain this used to work but now I get Data type mismatch in criteria expression.

Any ideas?

EDIT

Anyone willing to test the sql against their own data?

Upvotes: 2

Views: 4088

Answers (2)

Colophi
Colophi

Reputation: 153

Is the city stored as a string? If its stored as a number (perhaps as an fkey to another table) then this would make sense as you're using string operations on it.

Edit: Note to self: stop forgetting about Null values.

Upvotes: 3

ic3b3rg
ic3b3rg

Reputation: 14927

The problem turned out to be a null value in the column.

The solution is:

SELECT [city]
FROM [patient]
WHERE REPLACE(
  IIF(ISNULL([patient].[city]),'',[patient].[city])
  ,'  ',' ') LIKE 'San D*'

Upvotes: 5

Related Questions