Nested DLookup in SQL UPDATE

[Adam McReynolds]

I have a nested DLookup in a SQL UPDATE that is not working. The DLookup has an "AND" in the WHERE Statement as well. I cannot figure out the problem. I got a type mismatch with this code and when I take out the single quotes I do not get an error message but the data is not updated in the table. Any suggestions?

SQLstock1 = "UPDATE TBL_STOCK SET Stock =  '" & DLookup("RemainingQty", "QRY_STOCK", "ActionEntity = '" & Me.cmb_customer1 & "'" And "StockType = '" & Me.cmb_stock_type1 & "'") & "' WHERE StockEntity = '" & Me.cmb_customer1 & "'"

 

[ozinm]

are RemainingQty & stock numbers or text?
IF they are numbers, you should remove the single quote marks i.e. '

also if dlookup can't find a matching record, I think it returns a null value so you'll need to handle that. also, the me.cmb_cutomer1 & me.cmb_stock_type1 might contain nulls as well.
The easiest way to handle these is probably using nz.
Something like this should do:
nz(me.cmb_cutomer1,"0")
this will return the value of customer1 if it's not null, otherwise it will return 0