Replace Statement?

[Jason]

In SQL Server the following statement works:

SELECT * FROM [MyTable] WHERE Replace([FieldName], '1', '2') = 'Whatever'

However, it does not work in Access (2003); instead it generates a "Data
Type mismatch" error. Does the 'Replace' statement not work in Access? I
tried switching the apostrophes to quotation marks but this didn't help.

Thanks.

 

[Brendan Reynolds]

Probably one or more records contain a Null value in that field. Try
something like ...

SELECT Table1.Test_Text
FROM Table1
WHERE (((Replace([Test_Text] & ""," ",""))="onetwo"));

Note the empty string appended to the value of the field, to coerce the
result to a string.

Alternatively, you could use the NZ function if you prefer ...
Replace(NZ([TestText], "") ... etc.

 

[Jason]

Thanks. However, it appears that the "Replace" command just doesn't work
when it's in the Where clause. It works fine if I say "Select
Replace(FieldName, 'A', 'B') From MyTable" but doesn't work if I say

"Select Replace(FieldName, 'A', 'B') From MyTable Where Replace(FieldName,
'A', 'B') Is Not Null".

I will keep playing around with it though.

Probably one or more records contain a Null value in that field. Try
something like ...

SELECT Table1.Test_Text
FROM Table1
WHERE (((Replace([Test_Text] & ""," ",""))="onetwo"));

Note the empty string appended to the value of the field, to coerce the
result to a string.

Alternatively, you could use the NZ function if you prefer ...
Replace(NZ([TestText], "") ... etc.

--
Brendan Reynolds (MVP)

In SQL Server the following statement works:

SELECT * FROM [MyTable] WHERE Replace([FieldName], '1', '2') = 'Whatever'

However, it does not work in Access (2003); instead it generates a "Data
Type mismatch" error. Does the 'Replace' statement not work in Access? I
tried switching the apostrophes to quotation marks but this didn't help.

Thanks.

 

[Brendan Reynolds]

I tested my example before I posted it. It works for me. Have you actually
tried what I recommended? Your most recent example is not doing what I
advised, and would be subject to exactly the same problem I described - it
will fail if the field contains any Null values.

In addition, note that the return value of the Replace function is also a
string, and therefore can never be Null.

In summary, you need to coerce any Null values to strings before passing the
value to the Replace function. Attempting to pass Null values and then check
the return value of the function for Null, as your most recently posted
example is doing, is not going to work.

--
Brendan Reynolds (MVP)

Thanks. However, it appears that the "Replace" command just doesn't work
when it's in the Where clause. It works fine if I say "Select
Replace(FieldName, 'A', 'B') From MyTable" but doesn't work if I say

"Select Replace(FieldName, 'A', 'B') From MyTable Where Replace(FieldName,
'A', 'B') Is Not Null".

I will keep playing around with it though.

Probably one or more records contain a Null value in that field. Try
something like ...

SELECT Table1.Test_Text
FROM Table1
WHERE (((Replace([Test_Text] & ""," ",""))="onetwo"));

Note the empty string appended to the value of the field, to coerce the
result to a string.

Alternatively, you could use the NZ function if you prefer ...
Replace(NZ([TestText], "") ... etc.

--
Brendan Reynolds (MVP)

In SQL Server the following statement works:

SELECT * FROM [MyTable] WHERE Replace([FieldName], '1', '2') =
'Whatever'

However, it does not work in Access (2003); instead it generates a "Data
Type mismatch" error. Does the 'Replace' statement not work in Access? I
tried switching the apostrophes to quotation marks but this didn't help.

Thanks.