Update query with multiple Sets and Where conditions

**JasonBarnes** · 12-15-2010, 09:10 AM

UPDATE TestData, BusinessRules1

SET TestData.DESTINATIONSTATE = BusinessRules1.DESTINATIONSTATE And TestData.DESTINATIONCITY=BusinessRules1.DESTINATIONCITY AND TestData.[#BUSRULE]=1

WHERE TestData.NAMEFUNCTION=BusinessRules1.NAMEFUNCTION And TestData.CITY=BusinessRules1.CITY And TestData.ST=BusinessRules1.ST And TestData.COM4_DESC=BusinessRules1.COM4_DESC And TestData.USPORT=BusinessRules1.USPORT And TestData.FPORT=BusinessRules1.FPORT And TestData.ULTPORT=BusinessRules1.ULTPORT And TestData.SLINE=BusinessRules1.SLINE;

I don't receive any errors when saving this query. But when I run it, it doesn't pick up any rows to update as it should based on previous VLOOKUPs in excel with the same data. My guess is there are [s or (s I should be including.

**alansidman** · 12-15-2010, 11:55 AM

As a general rule, I like to build my queries in the QBE. Test them and then if I need to run them through VBA, then I copy and paste the SQL statement into code. Having said all that, have you run these queries from the QBE? If yes, did you get expected results. Also, before making a query an update query, I usually run it as a select query, so that I can see what is found before updating. An important bit of advise, if you are not doing this, is make a copy of the original table and save it as a back up so that if your updates are wrong in test you can replace the table quickly. It would be helpful also, if you were to post your db, so that we could look and see what other factors are affecting what you are attempting to do. Make sure to post it with only dummy (non-confidential) data. Also, if you have been doing lots of form and table changes, do a compact and repair prior to uploading.

I apologize if it seems like I am ranting here.

Alan

**JasonBarnes** · 12-15-2010, 01:20 PM

Just figured it out.

Thanks for the advice. I should have probably went the SELECT route first to make sure it was picking up matches. There was an space before my STs values. I had to delimit this from one huge field and in the process didn't catch the space. That was cause it not to pick up the matches.