Deleting commas within quotes

[RJF]

I have a table that contains one field. I will be breaking this field into
several other fields by replacing the commas with semi-colons and then
exporting the table to a text document.

The problem I'm having is when there are any commas within quotes, as shown
below. How do I remove these commas before I replace all the other commas
with semi-colons?

Below is a sample row containing the extra commas. There are three fields
here.

333371,"http://maps.google.com/maps?+Broadway,+Providence,+RI+",JRaposo,

The row should look like this before I replace the commas with semi-colons.

333371,"http://maps.google.com/maps?+Broadway+Providence+RI+",JRaposo,

I know it probably doesn't make sense that I'm changing the commas to
semi-colons. It was an attempt to solve the commas w/in quotes problem (long
story). I could remove the step of replacing the commas with the
semi-colons, if I could just remove the commas between the quotes.

I hope I've made that clear enough. Any help would be so much appreciated.

Thank you,

 

[KARL DEWEY]

It appears that the unwanted commas are always followed by a plus sign so
then do this --
Replace([Yourfield], ",+", "+")
Then to replace the other commas with semicolons use this --
Replace(Replace([Yourfield], ",+", "+"), ",", ";")

 

[RJF]

Hi Karl,

No, they aren't always followed by a plus sign. I just happened to grab
that line as an example.

I think I just got it working. It appears to be anyway.

I created an update query where I'm updating the 2nd field with the quotes
around it. I'm updating field1 with the following:

Left([field1],InStr(1,[field1],Chr(34))-1) &
replace(Mid([field1],InStr(1,[field1],Chr(34)),InStrrev([field1],Chr(34))-7),","," ") & Mid([field1],InStrrev([field1],Chr(34))+1,1000)

Oh yeah, the quote field will always start at position 8.

I know there must be a much, much easier way. If you can think of anything,
please let me know. If not, I'll just go with what I have.

Thanks so much for the quick reply.

--
RJF

It appears that the unwanted commas are always followed by a plus sign so
then do this --
Replace([Yourfield], ",+", "+")
Then to replace the other commas with semicolons use this --
Replace(Replace([Yourfield], ",+", "+"), ",", ";")
--
KARL DEWEY
Build a little - Test a little

I have a table that contains one field. I will be breaking this field into
several other fields by replacing the commas with semi-colons and then
exporting the table to a text document.

The problem I'm having is when there are any commas within quotes, as shown
below. How do I remove these commas before I replace all the other commas
with semi-colons?

Below is a sample row containing the extra commas. There are three fields
here.

333371,"http://maps.google.com/maps?+Broadway,+Providence,+RI+",JRaposo,

The row should look like this before I replace the commas with semi-colons.

333371,"http://maps.google.com/maps?+Broadway+Providence+RI+",JRaposo,

I know it probably doesn't make sense that I'm changing the commas to
semi-colons. It was an attempt to solve the commas w/in quotes problem (long
story). I could remove the step of replacing the commas with the
semi-colons, if I could just remove the commas between the quotes.

I hope I've made that clear enough. Any help would be so much appreciated.

Thank you,