How do I do a replace for a number of records?

[Nam22]

I am importing a file which will contain about 20 records (20 fields per row)
into an access database table.
I want to be able to automate doing a REPLACE across all the records I
have imported.
(because. in order to make the import work, in the Excel csv file I replaced
all commas with the word 'comma'. I now want to replace the word 'comma' with
actual ',').
Do I make sense?

any help very gratefully received.
thanks
Nicola
Access 2007

 

[KARL DEWEY]

Continue this SQL string for all 20 fields using your table and field names.
UPDATE 1st_Table SET [1st_Table].Field1 = Replace([Field1],"comma",","),
[1st_Table].Field2 = Replace([Field2],"comma",","), [1st_Table].Field3 =
Replace([Field3],"comma",",");

 

[fredg]

I am importing a file which will contain about 20 records (20 fields per row)
into an access database table.
I want to be able to automate doing a REPLACE across all the records I
have imported.
(because. in order to make the import work, in the Excel csv file I replaced
all commas with the word 'comma'. I now want to replace the word 'comma' with
actual ',').
Do I make sense?

any help very gratefully received.
thanks
Nicola
Access 2007

Back up your data first.
Did you have a space before and after the word comma?
Do you wish just a space after the ,?

Update YourTable Set YourTable.FieldName= Replace([FieldName]," comma
",", ")

 

[Jeff Boyce]

Nicola

Seems a bit circuitous.

Couldn't you just save the Excel as a tab-delimited .txt file, then import
that?

When I tried it just now, the commas (",") in my original Excel spreadsheet
came through fine into Access.

And when I tried linking to the original Excel spreadsheet from within
Access, the commas came through without incident.

Why do you believe you need to:
1) import Excel data rather than link to it, and
2) modify the contents before and after retrieving it in Access?

Regards

Jeff Boyce
Microsoft Office/Access MVP