Replace works in one DB, error in another

[Cormorand]

Hi, I am new to this forum, and am still using Access 2003.

The following code in a query design window:

MyNewString: Replace([MyString],"a","x")

replaces all a's with x's in one .mdb database on my computer, but in another similar database it displays the error "Syntax Error in the Visual Basic-Module", and the word "Replace" is highlighted.

Why might two .mdb databases in the same folder work so differently? I would think they would be using the same libraries and references.

 

[CJ_London]

The error implies there is something wrong with your construction.

In the db where it fails, check that [MyString] is actually a string

 

[Cormorand]

CJ, thank you for your response. I have now imported the table from one DB into the other, and have exactly the same table and query in both databases. The Replace function causes an error message in one, and works perfectly in the other. The only difference in the two databases which I can see is that one is 400 KB in size, the other 3 MB. Same computer, same installation of MS Office 2003. (I had to reinstall everything on my computer from scratch two weeks ago, I'm going to see if there is an Office 2003 Service Pack I may have missed...)

 

[CJ_London]

you may have a damaged table - try compacting and recompiling the code

Are you sure the two tables are identical? Same field names and field types

 

[Cormorand]

just installed SP 3, problem remains.

 

[Cormorand]

Finally found the source of the trouble: one database contained an old module with defective code (some lines were red) which had long since been discarded but never deleted. It had gone unnoticed because it had never affected the operation of the DB until I tried to add these functions. Now after removing the old module, everything works the way it should. My mistake. Thanks for your suggestions!

 

[spikepl]

Try using VBA.Replace instead of Replace. If that works then your references got screwed up, so I'd open a new db and import everything into that. If it doesn't work, try some of the other common string functions like Len or Left. ANd try importing everything into a new db.