Puzzling error in VBA db.execute statement

[John_G]

Using A2010 in Windows 7.



This one really has me stumped. Can anyone see anything wrong with this statement?

currentdb.Execute "Update Text_File_FF set Statement_text = replace(Statement_Text,' ',' ')",dbfailonerror"
or
currentdb.Execute "Update Text_File_FF set Statement_text = replace(Statement_Text,"" "","" "")",dbfailonerror

I want to change all occurrances of a double space in the Statement_Text field to a single spaces. Statement_Text is Text 200 data type.

It worked fine in A2003 on Windows XP, but now both versions are giving me the "data type mismatch in criteria expression" error.
(To test, I used "A" and "B" instead of blanks - same error)

I checked all the names, and they are OK - I get different errors otherwise.

Any ideas?

Thanks.

[June7]

I removed the dbfailonerror parameter and the update works.

[orange]

The quote after dbFailOnError seems extraneous.

[June7]

Maybe that was a post typo, because I still got error even after correcting for that.

[amrut]

currentdb.Execute "Update Text_File_FF set Statement_text = replace(Statement_Text,"" "","" "")",dbfailonerror

The second one is correct but you have used single space for both the parameters in replace function, the first should be double space.

CurrentDb.Execute "UPDATE Text_File_FF SET Statement_text = Replace(Statement_text,""  "","" "")", dbFailOnError

But in that case the query will not update but should not throw an error.

[John_G]

Good morning all -

I found the problem and the solution.

After looking at numerous results from a google search on this issue, I found a posting that said Replace does not like Null as its first argument; the VBA help confirms that. (Talk about a misleading error message!)


I changed the code to:

currentdb.Execute "Update Text_File_FF set Statement_text = replace(nz(Statement_Text,''),' ',' ')", dbfailonerror

and it works without a problem.

Thanks!

[June7]

amrut, I am sure OP used double space but the forum programming does not allow repetitive spacing in regular text. Must be within CODE tags.

John_G, thanks for sharing the solution. The cause seems so obvious now.