Update Query Not Working

[needhelpplz2]

Hello all,

UPDATE TblInvoice SET TblInvoice.VCTBatch = DLookUp("[VCT Year]","[VCTBatch]","[Number] =" & 1) & "-" & DLookUp("[Increment]","[VCTBatch]","[Number] =" & 1)
WHERE (TblInvoice.RecipientID IN (SELECT TOP 20 TblInvoice.RecipientID FROM TblInvoice WHERE TblInvoice.VCTBatch IS NULL AND TblInvoice.Isclosed = True AND (Dlookup("[Payment Method Look up Code]", "[TblSupplier]", "ID =" & tblinvoice.SupplierID) = "EFT"));


For some reason i'm getting an syntax operator error "missing" or something near the end, where it says "ID = " & tblinvoice.SupplierID. Would appreciate any help I could get, thanks!

 

[CJ_London]

difficult to read your code due to the poor layout but it looks like you are missing a FROM table and your sub quer is missing an alias

 

[namliam]

Update queries dont need a FROM clause...
Reformatted the query to make it more readable

UPDATE TblInvoice 
SET TblInvoice.VCTBatch = DLookUp("[VCT Year]" ,"[VCTBatch]","[Number] =" & 1) & "-" & 
                          DLookUp("[Increment]","[VCTBatch]","[Number] =" & 1)
WHERE (TblInvoice.RecipientID IN (SELECT TOP 20 TblInvoice.RecipientID 
                                  FROM  TblInvoice 
                                  WHERE TblInvoice.VCTBatch IS NULL 
                                    AND TblInvoice.Isclosed = True 
                                    AND (Dlookup("[Payment Method Look up Code]", "[TblSupplier]", "ID =" & tblinvoice.SupplierID) = "EFT"
                                        )   
                                 );

I can spot the problem, can you?

 

[needhelpplz2]

The reformatted code is missing a bracket at the end, however i still get the missing operator error in query expression '[ID] = '. Still can't find the error

 

[namliam]

Is tehre a record where SupplierID is null?

 

[needhelpplz2]

Thanks lol that helped a lot and it worked! And sorry about the bad formatting I copied directly from SQL view and I'm pretty new to access so I didn't know how to format.

 

[namliam]

No problem we all started where you are now, just remember readable code is maintanable code....

Plus when posting (any) code on a(ny) forum make sure to use the code tags (# in the post menu)

 

[needhelpplz2]

strSQL = "UPDATE TblInvoice SET IsClosed = '-1' WHERE Invc_Number = '&Forms!Main!InvoiceNumber'"

This is much shorter lol XD. I seem to be having trouble with my update queries in general. All fields are valid and the invoicenumber being referenced from main is also being passed properly. For some reason, the checkboxes remain unchecked. The command used to execute it was

CurrentDb.Execute strSQL, dbFailOnError

I got no errors of any kind. Any ideas?

 

[namliam]

if isclosed is a boolean (checkbox as you call it) you should not use ' around your -1
and if you substitute in variables.... you do this OUTSIDE of the string, rather than inside.... exactly like you did on the DLookup above.

Please use code tags when posting code, use the # on the post menu.

 

[needhelpplz2]

Hello,

 strSQL = "UPDATE TblInvoice SET IsClosed = -1 " & " WHERE Invc_Number = " & Forms!Main!Invoice!Invc_Number

I have tried this too and it still gives me a data type mismatch :banghead:

 

[needhelpplz2]

Never mind, fixed it, needed ' quotes around the other one haha... Thanks for all your help