Syntax Error

[tagteam]

I am trying to get this query working. I want to update line items where the BID - xxxx And the URC is not 7 or1. I always get a bit confused in how to do multiple criteria like this.



Currently I have tried:

strSQL = "Update tbl_ImportedRepairs " _         
& "Set tbl_ImportedRepairs.AccountingCode = 21570 " _
         & "WHERE [tbl_ImportedRepairs].[BillingInvoiceId]= paramBID AND [UpdatedResponsibilityCode] = 7 OR [UpdatedResponsilityCode =1"

And

Update tbl_ImportedRepairs Set tbl_ImportedRepairs.AccountingCode = 21570  WHERE [tbl_ImportedRepairs].[BillingInvoiceId]= "ALX-LB10802" AND [tbl_importedRepairs].[UpdatedResponsibilityCode] Not In (3,6)"

The first one is my parameterized query and works fine if I only use the where BillingInvoiceID ANd UpdatedResponsibility code but fails when I add the OR [UpdatedResponsilityCode =1"

The second one I am trying to manually put in the values to test but using the not in
Thanks

[Micron]

Did you debug.print your sql variable and examine in the immediate window? That is basic troubleshooting 101.
If you did and it looked good, then your eyes are missing something. Then you copy/paste the output into a new query in sql view and switch to datasheet view. Offending part is usually highlighted in sql view. The error message might actually pinpoint the exact cause.

Do you see the problem?
[UpdatedResponsilityCode = 1

[tagteam]

Yes, I tried debug but it just gives me the whole line and doesn't who where the problem is. When I set it to use not in (3,6) it doesn't give any error at all but doesn't update the value like it is supposed to. The form just kind of blinks and nothing happens.

Yes, sorry I corrected that bracket a while ago so that was not the issue.

[tagteam]

Hey guys, Sorry, it appears it was a problem with access. I had copied the database to be able to test and somehow the the table for the data got messed up and when you went to the link section it was showing a bunch of numbers and not the table name. It s working with this:

"WHERE [tbl_ImportedRepairs].[BillingInvoiceId]= paramBID AND tbl_IMportedREpairs.[UpdatedResponsibilityCode] = 7 OR tbl_ImportedRepairs.UpdatedResponsibilityCode =1"

[Welshgasman]

Do yourself a favour.
As recommended Debug.Print the sql string and copy and paste into the SQL window, when you cannot see the error after the Debug.Print
Also I always start with a space at the start of the next concatenated string. That way you can see straightaway that a space exists, rather than scrolling all the way to the far right.

[CarlettoFed]

Maybe it should be:

"WHERE [tbl_ImportedRepairs].[BillingInvoiceId]= paramBID AND (tbl_IMportedREpairs.[UpdatedResponsibilityCode] = 7 OR tbl_ImportedRepairs.UpdatedResponsibilityCode = 1)"

[Welshgasman]

Maybe it should be:

"WHERE [tbl_ImportedRepairs].[BillingInvoiceId]= paramBID AND (tbl_IMportedREpairs.[UpdatedResponsibilityCode] = 7 OR tbl_ImportedRepairs.UpdatedResponsibilityCode = 1)"

That is how I would write it as well.