Comma Replacement Query

[DFowler]

Prob a real simple answer to this issue that my colleague is having, Is there a way within an updat query that ",," can be replaced with "," we have tried placing these into an update query but it doesn't seem to pick up the ",," as they are mid string on the table.

 

[Jemmo]

I think you need to create a new field in your table and run an update query.

The following example takes the first part of the string up to the double commas, and concatenates with the second part of the string 1 character after 2nd comma (I think. Have a play with it.

UPDATE Table
SET Table.FieldNew = (Mid(Table.Field, 1, Instr(Table.Field, ",,", 1)) ||
(Mid(Table.Field, Instr(Table.Field, ",,", 1)+2), Len(Table.Field))

 

[Rabbie]

I think you need to create a new field in your table and run an update query.

The following example takes the first part of the string up to the double commas, and concatenates with the second part of the string 1 character after 2nd comma (I think. Have a play with it.

UPDATE Table
SET Table.FieldNew = (Mid(Table.Field, 1, Instr(Table.Field, ",,", 1)) ||
(Mid(Table.Field, Instr(Table.Field, ",,", 1)+2), Len(Table.Field))

That won't work because InStr has the wrong parameters. You need to amend the Instr to be

Instr( ,Table.Field, ",,", 1)+2

 

[Jemmo]

Nearly right then. Not bad for an amateur.

 

[DFowler]

Right have tried all the above and the attached is the error that is now being received

 

[Rabbie]

Firstly you have not corrected the inStr as I suggested. It WONT work as you have it just now. I am not going to plough through the rest of your stuff till you correct the InStr functions.

 

[DFowler]

thankyou you for your pleasant response but I directly copied your fix that u suggested but this caused other errors which were only fixed via the removal of the first comma in front of the table name and the addition of the second bracket after the 2

 

[Rabbie]

thankyou you for your pleasant response but I directly copied your fix that u suggested but this caused other errors which were only fixed via the removal of the first comma in front of the table name and the addition of the second bracket after the 2

You're welcome.

If you look up InStr in Access/VBA help you will see tyhat there is a first optional parameter that is numeric. You cannot put a string parameter as the first parameter. The help information will give you the allowed values for the parameters. If it won't accept a null value then set the first parameter to 1. That will start the search from the beginning of the string.

 

[Jemmo]

Ah yes - Rabbie is right.

The correct syntax should be (I think);

UPDATE Table
SET Table.FieldNew = (Mid(1, Table.Field, Instr(1, Table.Field, ",,", 1)) ||
(Mid(Table.Field, Instr(1, Table.Field, ",,")+2), Len(Table.Field))


(sorry!)

 

[DFowler]

So in essence if i understand what your saying, I should be using

UPDATE Table
SET Table.FieldNew = (Mid(Table.Field, 1, Instr( ,Table.Field, ",,", 1)+2​

(Mid(Table.Field, Instr( ,Table.Field, ",,", 1)+2, Len(Table.Field))

and if that fails i should be using

UPDATE Table
SET Table.FieldNew = (Mid(Table.Field, 1, Instr( 1,Table.Field, ",,", 1)+2​

(Mid(Table.Field, Instr( 1,Table.Field, ",,", 1)+2, Len(Table.Field))

Feel free to correct me if i am wrong as i am basically acting as a middle man between the person with the query and you helpful chaps

 

[DFowler]

Ok will give that a go as u responded to my question before i posted it which i gotta admit is quite a talent

 

[Jemmo]

Yes, your syntax is correct, mine has an error in the Mid clause

 

[DFowler]

Next installment in the never-ending query i am using the below code :-
UPDATE Addressdata SET TAddressdate.AddressSearch2 = (Mid(Addressdate.AddressSearch, 1, Instr( 1,Addressdate.AddressSearch, ",,", 1))+2
(Mid(Addressdate.AddressSearch, Instr( 1,Addressdate.AddressSearch, ",,", 1)+2, Len(Addressdate.AddressSearch))));

but am receiving the attached error, i had to once again add some brackets into the code as it would not allow me to safe it due to a syntax error without the additional brackets

 

[Rabbie]

Next installment in the never-ending query i am using the below code :-
UPDATE Addressdata SET TAddressdate.AddressSearch2 = (Mid(Addressdate.AddressSearch, 1, Instr( 1,Addressdate.AddressSearch, ",,", 1))+2
(Mid(Addressdate.AddressSearch, Instr( 1,Addressdate.AddressSearch, ",,", 1)+2, Len(Addressdate.AddressSearch))));

but am receiving the attached error, i had to once again add some brackets into the code as it would not allow me to safe it due to a syntax error without the additional brackets

No attachment. It seems to have got lost in cyberspace When I see the error I will have a look at it.

 

[DFowler]

Hmm how strange the error is attached (hopefully)

 

[Rabbie]

Hi there seemed to be a couple of typos in your SQL. I have fixed them and tested this and it works on a test system for me

You may find you need to change Adressdate to Addressdata throughout as I wasn't sure of your table name.


Query is

UPDATE Addressdate SET Addressdate.AddressSearch2 = Mid(Addressdate.AddressSearch, 1, Instr( 1,Addressdate.AddressSearch, ",,", 1))   & Mid(Addressdate.AddressSearch, Instr( 1,Addressdate.AddressSearch, ",,", 1)+2, Len(Addressdate.AddressSearch)) WHERE Addressdate.addressSearch like "*,,*";

Good luck

 

[boblarson]

Just curious as to why you just don't use the Replace function in the update query:

Replace([YourFieldName], ",,", ",") As NewFieldName

 

[Rabbie]

Just curious as to why you just don't use the Replace function in the update query:

Replace([YourFieldName], ",,", ",") As NewFieldName

Because I had just corrected the same syntax error in another of Jemmo's posts. I just had a blind spot and was trying to get his solution to work.

Replace would have been a much simpler and better solution.