Replace Query

[vb4me]

I am trying to use an update query with a field stone_code.

In the update to area I have
replace([stone]![stone_code],[forms]![frmStone Replace]![stone],[forms]![frmStone Replace]![newstone])

so in theory it should be looking at the field stone for whatever is entered in the form frmStone Replace field and replacing it with the data in the newstone field from the form frmStone Replace.

What is happening is I get an error :

wrong number of arguments used with function in query expression.

Any ideas?

 

[dhookom]

There was a version of Access 2000 in which Replace() did not work in queries. Have you tried to hard-code some values rather than using the control values? Do you have the latest service packs installed?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vb4me]

Duane,

I have tried it in 2003 and 2002 latest SP's.

I have substituted the links to the forms with hard code e.g I have a field be-001 and I want to replace the be with bb so I have used replace("stone_code","be","bb&quot

This gives the same error message. Also tried two difeent PC's.

Does it matter that I am only trying to chane a part of the stone_code field?

I may have to use code to do a find and replace - I have seen it around and might have a play with it but if this worked it would be much simpler.

VB4ME

 

[dhookom]

Paste your full sql view of your query into a response.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[vb4me]

Duane,

here you go :

UPDATE STONE SET STONE.STONE_CODE = Replace([STONE]![STONE_CODE],[Forms]![frmSTONE REPLACE]![STONE],[Forms]![frmSTONE REPLACE]![NEWSTONE]);

or

UPDATE STONE SET STONE.STONE_CODE = Replace([STONE]![STONE_CODE],"be","bb"

both give exactly the same error.

Stone code is a primary key in the stone table if that makes any difference.

Here is an example of the field I am attempting to change :

BE-R3.5
BE-R4
BE-R4.5
BE-R5
BE-R5.5 etc

thnaks for looking into this for me.

VB4ME

 

[dhookom]

Do you have a user defined function named "Replace"? The SQL works for me. Does your code compile?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[RoyVidar]

Hi!

Just another set of eyes... The reference to [SONE]![STONE_CODE] within the replace function - what do you refer to?

If it's the PK of the table, then stone.stone_code (dot not bang) should do, or just stone_code.

[tt]UPDATE STONE SET STONE.STONE_CODE = Replace(STONE.STONE_CODE,"be","bb"[/tt]

HTH Roy-Vidar

 

[vb4me]

Thanks for your help Duane - I feel like an idiot now - yes I had an old module called replace from some legacy code.