Too few Parameters Expected Error 2

[vix666]

I have an error which appears when i run my code, i have had a look at it and i dont know whats wrong, so any help would be gladly appreciated

Many Thanks

Vicky

strSQL = "SELECT Count([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot) AS Month " & _
" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between '#[Forms]![Print Request between dates]![sdate]#'" & _
" And '#[Forms]![Print Request between dates]![edate]#')" & _
" AND (([Complaints Table].[Attributable to Company])=""epco"&quot)" & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot)" & _
" ORDER BY [Complaints Table].[Type of Complaint], Month;"

 

[jebry]

Hi Vicky!

Try this:

strSQL = "SELECT Count([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot) AS Month " & _
" FROM [Complaints Table]" & _
" WHERE ((([Complaints Table].Date) Between #" & [Forms]![Print Request between dates]![sdate] & "#" & _
" And #" & [Forms]![Print Request between dates]![edate] & "#)" & _
" AND (([Complaints Table].[Attributable to Company])=""epco"&quot)" & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot)" & _
" ORDER BY [Complaints Table].[Type of Complaint], Month;"

hth


Jeff Bridgham
bridgham@purdue.edu

 

[nicsin]

Hi vicky,

sorry to find out you did not solve your problem. When you add # you have to loose the '.

I think this will work. Post back if it doesn't

strSql = "SELECT Count([Complaints Table].Counter) AS CountOfCounter," & _
[Complaints Table].[Type of Complaint]," & _
Format([Date],""mm"&quot AS Month " & _
" FROM [Complaints Table]" & _
" WHERE [Complaints Table].Date Between #[Forms]![Print Request between dates]![sdate]#" & _
" And #[Forms]![Print Request between dates]![edate]#)" & _
" AND [Complaints Table].[Attributable to Company] = ""epco"" " & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _
" ORDER BY [Complaints Table].[Type of Complaint], Month"



regards,
nicsin

 

[vix666]

Thanks for the help. I tried both bits of code, the last one solves the too few parameters error but then i get another error on the bit below, saying "syntax error in date in query expression"

WHERE [Complaints Table].Date Between #[Forms]![Print Request between dates]![sdate]#" & _
" And #[Forms]![Print Request between dates]![edate]#)" &

Any other ideas?

Thanks for all your help

Vicky

 

[nicsin]

oops... delete the parenthesis after [edate]# and it will work.

 

[vix666]

nope doesnt work

Any other suggestions?

Thanks

Vicky

 

[nicsin]

What is the Date inside the format function? As far as I know Date is a reserved keyword which cannot be used. you should rename it and try this:

strSql = "SELECT Count([Complaints Table].Counter) AS CountOfCounter," & _
" [Complaints Table].[Type of Complaint]," & _
" Format([Date],""mm"&quot AS Month " & _
" FROM [Complaints Table]" & _
" WHERE [Complaints Table].Date Between #" & [Forms]![Print Request between dates]![sdate] & "#" & _
" And #" & [Forms]![Print Request between dates]![edate] & "#" & _
" AND [Complaints Table].[Attributable to Company] = ""epco"" " & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _
" ORDER BY [Complaints Table].[Type of Complaint], Month"

 

[jebry]

Hi Vicky!

Let's try again:

strSQL = "SELECT Count([Complaints Table].Counter) AS CountOfCounter, [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot AS Month " & _
" FROM [Complaints Table]" & _
" WHERE [Complaints Table].Date) Between #" & [Forms]![Print Request between dates]![sdate] & "#" & _
" And #" & [Forms]![Print Request between dates]![edate] & "#" & _
" AND [Complaints Table].[Attributable to Company]=""epco""" & _
" GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot" & _
" ORDER BY [Complaints Table].[Type of Complaint], Format([Date],""mm"""

See if this works!




Jeff Bridgham
bridgham@purdue.edu

 

[vix666]

Thanks alot for that, this sql has been bugging me for ages Thankyou both for all your help so a star for you both.

Thanks Again

Vicky

 

[nicsin]

thnx vicky! all the best

nicsin