Run Time error 3061 - SQL from Cmd Button. 1

[Poduska]

I have a working Query I am trying to get to work in VB from a command button. All I need to do is sum the value of one field from one table and send that number to a variable. Seems easy but I always have to fight access when I stick SQL in VB. I declare and populate 4 variables, Item, ADC, Segment and [merch Code]. These all seem to work as I verified them with msgbox entries before the openrecordset line (see before "Set Command" below).

The query is finding, grouping and suming up the field [max qty] for all records matching the critera in the Having clause. It all seems simple enough and works just fine in an actual Access query.

When I run it I get

Run-Time error '3061':
Too few parameters. expected 1



SQL = "SELECT" _
& " Tbl_New_Release_With_Max.ITEM," _
& " Tbl_New_Release_With_Max.ADC," _
& " Tbl_New_Release_With_Max.SEGMENT," _
& " Tbl_New_Release_With_Max.[Merch Code]," _
& " Sum(Tbl_New_Release_With_Max.[MAX QTY]) AS Total_Max_Qty" _
& " FROM" _
& " Tbl_New_Release_With_Max " _
& " GROUP BY" _
& " Tbl_New_Release_With_Max.ITEM," _
& " Tbl_New_Release_With_Max.ADC," _
& " Tbl_New_Release_With_Max.SEGMENT," _
& " Tbl_New_Release_With_Max.[Merch Code]" _
& " Having " _
& " (Tbl_New_Release_With_Max.ITEM=" & Item_Num & "" _
& " AND Tbl_New_Release_With_Max.ADC= " & ADC & "" _
& " AND Tbl_New_Release_With_Max.SEGMENT= " & Segment & "" _
& " AND Tbl_New_Release_With_Max.[Merch Code]= " & Merch_Code & ""

'MsgBox Segment
'MsgBox Item_Num
'MsgBox Merch_Code
'MsgBox ADC
Set Exclusive_Max = MyDB.OpenRecordset(SQL, DB_OPEN_DYNASET)

Any ideas?

Thanks
David

 

[Beejay57]

if the any of the variables are strings then you need
to use double quotations
for example if merch_code is a text value then
& " AND Tbl_New_Release_With_Max.[Merch Code]= " & """"& "Merch_Code" & """" & ""

 

[Poduska]

Well I got it to work, cant say I totally understand the need for 4 double quotes. I don't remember seeing THAT anywhere but if it works....

I did find it worked without the " around my variable name
so

& " AND Tbl_New_Release_With_Max.[Merch Code]= " & """"& "Merch_Code" & """" & ""

The red " was removed. Hope I did the right thing.

Thanks again

 

[Beejay57]

my mistake,because Merch_Code is a variable you dont
need the quotes around it.
If Merch_Code was a string that you were searching for
you would need the quotes
Glad you got it to work