commas

[peljo]

I am again having problems with these damned commas, they seem never to conform to my knowledge.I tried to convert the otherwise good and working update query into an sql but i got red colours showing that Access does not accept my commas.I tried with single commas ' or with double commas """" but obviously i jumbled the commas up with no result.Would you help me convert the update query below into an sql ?


UPDATE products SET products.items5 = DLookUp("Diff","qryNetDiff","ProductID=" & [ProductID])
WHERE (((products.Productid) In (SELECT ProductID FROM qryNetDiff)));

 

[ajetrumpet]

UPDATE products SET products.items5 = DLookUp("Diff","qryNetDiff","ProductID=" & [ProductID])
WHERE (((products.Productid) In (SELECT ProductID FROM qryNetDiff)));

Actually, I see more than one thing wrong here. First of all, the syntax for the function is this (or at least what I would use):

DLookUp("[color=red][b][size=5][[/color][/b][/size]Diff[color=red][b][size=5]][/b][/color][/size]","qryNetDiff","ProductID=[ProductID]")

Also, this portion...

"ProductID=" & [ProductID]

...is going to confuse the program, because you have brackets around the referenced portion of the clause, not the source portion. I don't know, but I wonder if that might be a problem too? And lastly, these two portions of code...

[b][u]"ProductID=" & [ProductID][/b][/u]

[I]AND[/I]

[U][B]WHERE (((products.Productid) In (SELECT ProductID FROM qryNetDiff)))[/B][/U]

...don't complement each other. If, in your DLookup function, you are referencing say, a form object that is called ProductID, and you include the WHERE clause that you have in the SQL, what happens if the value that you are referencing in the function is not in the recordset called qryNetDiff? You will probably get an error. Is that really what you want here?

 

[Rabbie]

You errors are not showing up in red so I dont know where you are getting your errors. However the Where clause in your query doesn't look quite right to me. You don't seem to set the selection criteria for products.Productid

 

[ajetrumpet]

You don't seem to set the selection criteria for products.Productid

Hey Rabbie, did you know that you don't have to reference table fields with the "." separator? A lot of times, I use:

"[fieldname] = "whatever" "

I found that out by accident a long time ago.

 

[ByteMyzer]

quotation marks, not commas

First of all, the puncuation marks to which you refer are called quotation-marks, not commas.

Second, there is nothing apparently wrong with the SQL statement, as presented out of context. Perhaps you could present it to us in the context of your application, along with the specific errors you are receiving, so that we would be better able to assist you.