How do I Replace '.' with '/'

[Lawrenceiow]

I have an Access2000 table with over 100,000 records and one of the fields is a text field which might contain a date entry. However, the database where the information comes from uses dots instead of slashes in dates (eg 01.01.2001 instead of 01/01/2001). So I need to replace each dot with a slash.

At the moment I use a macro to import my data (which is in the form of a tab delimited text file) using TransferText and then use 'RunCommand' with 'Replace' which opens the Find and Replace dialog box. I then have to type in . and / and change the 'Look In' to the field in question and change the 'Match' to 'Any Part of Field'.

This all works but is rather a naff way of doing it. I've tried searching these forums for a solution but whilst there are a lot on Search and Replace they are either not what I'm after or are too confusing for me to tell if they're what I'm after.

I've had a go at:

tables![myTable].[comments] = Replace(tables![myTable].[comments],".","/")

but this gives me an error saying Object Required.

I've also had a go with using SQL from various forums but I probably don't know enough about SQL in Access2000 to get it to work.

Please can someone help with a simple solution wether it be an Update Query or a Macro or in a procedure/function in a Module which I can call from the Macro.

 

[Fullerm40]

Hi Lawrence,

Try the following SQL, it should be what you're after:

UPDATE myTable SET myTable.[comments] = replace([Comments],".","/")
WHERE (((myTable.[comments]) Is Not Null));

Cheerio

Martin

 

[Lawrenceiow]

Thanks for you suggestion Martin, however the "replace" function is not listed in the expression builder otherwise I would have tried that already.

Any other ideas?

 

[Fullerm40]

Ahh...but it still works, try it I dare you..

Martin

 

[ghudson]

Did the Replace() function come out with Access 2000 or Access 2002? It is listed in the expression builder of Access 2003.

There is a custom Replace function floating around this forum somewhere.