Solved: Replace Function Error

[FrymanTCU]

Ok I'm sure someone has a better way to fix this problem but this is what I have been trying to do, if you have any suggestions please feel free to chime in. I have created a DAO recordset that determines what status I want to give the record when I insert it into my Log file. But the Address data has apostrophies,', in it which created an error.

So I am trying to use the Replace function to remove them but I get a null error. So then I tried to use and IIF(address is null , "", replace) and get object required error. Then I tried IIF(len(address)=0,"",replace) and get a null error. I don't know what to try next, do I need to assign the DAO fields to string variables and replace then or is there some other function I can use? Any help is greatly appreciated, also I am using Access 2000.

[vba]'" & IIf(Len(New_rst![MAILING ADDRESS 1]) = 0, "", Replace(New_rst![MAILING ADDRESS 1], "'", "")) & "'

'" & IIf(New_rst![MAILING ADDRESS 1]) is null, "", Replace(New_rst![MAILING ADDRESS 1], "'", "")) & "'[/vba]

Thanks,
Rich

[Oorang]

Try:
[VBA]Replace(NZ(New_rst.Fields("MAILING ADDRESS 1").Value,vbNullString), "'", vbNullString)[/VBA]

[FrymanTCU]

Aaron,

Great thanks for the help, this board is the most valuable VB site on the web!

Thanks again,
Rich