Update Query SQL Code Help!!!

[Crawfordrider33]

Hello,



I'm trying to right an update query that will look at a column of names and change some of the name that are listed as Smith, John to John Smith. The query I'm using is listed below:

UPDATE [1 - Closed Loan Rpts - Debriefs NEW]
SET [1 - Closed Loan Rpts - Debriefs NEW].Broker = StrConv(Right$([1 - Closed Loan Rpts - Debriefs NEW].Broker,Len([1 - Closed Loan Rpts - Debriefs NEW].Broker)-InStr(1,[1 - Closed Loan Rpts - Debriefs NEW].Broker,",")-1) & " " & Left$([1 - Closed Loan Rpts - Debriefs NEW].Broker,InStr(1,[1 - Closed Loan Rpts - Debriefs NEW].Broker,",")-1),3);

The issue I'm experiencing is that when I run the query it deletes all the names in the row except for the names that are listed as Smith, John. I only want it to change those names and leave the rest as is.

PLEASE HELP!!!

Thank you!

[JoeM]

You need to add Criteria to the query so that it only selects the names you wish to change.

A simple way to get this is to create a simple Select query, and play around with adding Criteria until you getting it selecting just the records you want to change. Then apply that same criteria to your Update Query here.

[Crawfordrider33]

Thanks Joe M! Unfortunately there a over 2000 different names in the data set. Is there anyway to apply an if statement to the update query that will only grab names with the "," in it? My ultimate goal is to write a query that will only change the names with the "," and just leave the rest be!

Thank you!

[JoeM]

Sure. Just have Criteria like this:
WHERE InStr(1,[NameField],",")>0;
which checks to see if some field ("NameField" in this example) contains a comma.