Access Replace Function

Question

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?

ic3b3rg · Accepted Answer · 2012-06-29 15:08:08Z

5

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*'

answered Jun 29, 2012 at 15:08

ic3b3rg

15k4 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ic3b3rg Over a year ago

That's quite helpful! Thanks!

Colophi · Accepted Answer · 2012-06-30 00:35:13Z

3

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.

edited Jun 30, 2012 at 0:35

answered Jun 29, 2012 at 2:12

Colophi

1531 gold badge1 silver badge9 bronze badges

2 Comments

Fionnuala Over a year ago

@ic3b3rg Are you absolutely sure it is a string? You have looked at the table itself in design view and ensured that a look-up table has not been used? I ask, because the query works for me.

ic3b3rg Over a year ago

@Remou The problem was a null value in the column - I added an answer with the solution.

Collectives™ on Stack Overflow

Access Replace Function

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related