VBA Code for Query Generated SQL Code 1

[bhujanga]

I have the following SQL code from a working Access Query. I'm going to want to run about eight very similar queries with one button click and so rather than having to have all of these queries defined I want to run the SQL code in VBA, but I can't get it to work due to not knowing how to convert the syntax properly. This is the SQL code:

UPDATE [Product Sales] SET [Product Sales].[Quantity Sold] = [forms]![Gift Set Converter]![BathSaltsSample]+Nz(DLookUp("[Quantity Sold]","Product Sales","[SalesReportDate]=forms![Gift Set Converter]![Report Date] and [Product]='Bath Salts (Sample)'"),0), [Product Sales].Revenue = [forms]![Gift Set Converter]![BathSaltsSampleRev]+Nz(DLookUp("[Revenue]","Product Sales","[SalesReportDate]=forms![Gift Set Converter]![Report Date] and [Product]='Bath Salts (Sample)'"),0)
WHERE ((([Product Sales].SalesReportDate)=[Forms]![Gift Set Converter]![Report Date]) AND (([Product Sales].Product)="Bath Salts (Sample)"));

Thanks.

 

[PHV]

eight very similar queries
Different on what ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bhujanga]

The eight queries differ only in the product they will be looking up values for. I'm only looking for help with the example query that I'm providing, once I have that figured out, I'll have no problem with the rest of them. My only question involves the syntax tweaks when you embed the SQL code you get from the Access Query Design environment to the VBA environment.
Here is a slightly simplified version of the code that I get from Access:

UPDATE [Product Sales] SET [Product Sales].[Quantity Sold] = [forms]![Gift Set Converter]![BathSaltsSample]+Nz(DLookUp("[Quantity Sold]","Product Sales","[SalesReportDate]=forms![Gift Set Converter]![Report Date] and [Product]='Bath Salts (Sample)'"),0)
WHERE ((([Product Sales].SalesReportDate)=[Forms]![Gift Set Converter]![Report Date]) AND (([Product Sales].Product)="Bath Salts (Sample)"));

Part of the problem lies in the quotes. I tried converting them to single quotes but that either wasn't the correct approach or there was still something additional I had to do.This is the way I tried it (using the full version of the query):

Private Sub Add_to_Sales_Click()
Dim strSQL1 As String
strSQL1 = "UPDATE [Product Sales] SET [Product Sales].[Quantity Sold] " & _
"= [forms]![Gift Set Converter]![BathSaltsSample]" & _
"+Nz(DLookUp('[Quantity Sold]','Product Sales','[SalesReportDate]" & _
"=forms![Gift Set Converter]![Report Date] and [Product]='Bath Salts (Sample)''),0)," & _
"[Product Sales].Revenue = [forms]![Gift Set Converter]![BathSaltsSampleRev]" & _
"+Nz(DLookUp('[Revenue]','Product Sales','[SalesReportDate]=forms![Gift Set Converter]![Report Date] and [Product]='Bath Salts (Sample)''),0)" & _
"WHERE ((([Product Sales].SalesReportDate)=[Forms]![Gift Set Converter]![Report Date]) AND (([Product Sales].Product)='Bath Salts (Sample)'))"
CurrentDb.Execute strSQL1, dbFailOnError
End Sub

Probably just an example of any update query with a where clause in it that uses literal text would be enough information to get me on the right track.

 

[PHV]

What about this ?

strSQL1 = "UPDATE [Product Sales] SET [Quantity Sold]" & _
"=[Forms]![Gift Set Converter]![BathSaltsSample]" & _
"+Nz(DLookUp(""[Quantity Sold]"",""Product Sales"",""SalesReportDate" & _
"=Forms![Gift Set Converter]![Report Date] and Product='Bath Salts (Sample)'""),0)," & _
"Revenue=[Forms]![Gift Set Converter]![BathSaltsSampleRev]" & _
"+Nz(DLookUp(""Revenue"",""Product Sales"",""SalesReportDate=Forms![Gift Set Converter]![Report Date] and Product='Bath Salts (Sample)'""),0)" & _
"WHERE SalesReportDate=[Forms]![Gift Set Converter]![Report Date] AND Product='Bath Salts (Sample)'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bhujanga]

I think this might do the trick - at least the code is getting through it without error, but I can't tell for sure because now I'm getting an error in the execute statement, which is identical to ones that I am using elsewhere without issue.
I.e.
CurrentDb.Execute strSQL1, dbFailOnError
It says it's expecting 3 parameters, yet the syntax helper doesn't list these - it just wants one and then allows for options.
Thanks, I appreciate your help.

 

[PHV]

OK, use this instead:

strSQL1 = "UPDATE [Product Sales] SET [Quantity Sold]=" & _
[Forms]![Gift Set Converter]![BathSaltsSample] & _
"+Nz(DLookUp(""[Quantity Sold]"",""Product Sales"",""SalesReportDate=#" & _
Forms![Gift Set Converter]![Report Date] & "# and Product='Bath Salts (Sample)'""),0)," & _
"Revenue=" & [Forms]![Gift Set Converter]![BathSaltsSampleRev] & _
"+Nz(DLookUp(""Revenue"",""Product Sales"",""SalesReportDate=#" & Forms![Gift Set Converter]![Report Date] & "# and Product='Bath Salts (Sample)'""),0)" & _
"WHERE SalesReportDate=#" & [Forms]![Gift Set Converter]![Report Date] & "# AND Product='Bath Salts (Sample)'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Much simpler:

strSQL1 = "UPDATE [Product Sales] SET [Quantity Sold]=" & _
[Forms]![Gift Set Converter]![BathSaltsSample] _
+Nz(DLookUp("[Quantity Sold]","Product Sales","SalesReportDate=" & _
"Forms![Gift Set Converter]![Report Date] and Product='Bath Salts (Sample)'"),0) & "," & _
"Revenue=" & [Forms]![Gift Set Converter]![BathSaltsSampleRev] & _
+Nz(DLookUp("Revenue","Product Sales","SalesReportDate=Forms![Gift Set Converter]![Report Date] and Product='Bath Salts (Sample)'""),0) & _
" WHERE SalesReportDate=#" & [Forms]![Gift Set Converter]![Report Date] & "# AND Product='Bath Salts (Sample)'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bhujanga]

Excellent! Actually it's the second one worked, but there was something wrong the last one. I'm not clear why the error message I was getting was on the Execute statement or what parameters it was referring to. I'll study the code you gave me for future 'competence'.
Thanks.