I have a VBA query which errors say

[vix666]

I have a VBA query which errors saying "Missing operator in query expression 'Format([Date],""mm"&quot AS Month'. Any ideas what is missing as I keep trying different things and im not getting anywhere?

Thanks in advance

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 [Type of Complaint], Format([Date],""mm"&quotAs Month; "

 

[JonFer]

Is there a space missing before the "As Month"?

ORDER BY [Type of Complaint], Format([Date],""mm"&quotAs Month

 

[vix666]

There wasnt a space but I have changed this and there is still the same error, any other ideas?

Thanks for the help

Vicky

 

[SQLBI]

Vicky,

Have you tried running it with only single " marks in your code?

ORDER BY [Type of Complaint], Format([Date],"mm")As Month

I'm only guessing here though.


Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[vix666]

Yep i've tried single " marks and that errors on the marks saying about is doesnt like being the end of the sentence.
Ive tried ' marks and it doesnt work

Thanks for the suggestion though

Any other ideas

Vicky

 

[nicsin]

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], month" & _

" ORDER BY [Type of Complaint], Month "

regards,
nicsin

 

[JonFer]

nicsin has it right for the ORDER BY - Take out the "As Month" (completely missed that before).

I think the group by was okay in your first post.

GROUP BY [Complaints Table].[Type of Complaint], Format([Date],""mm"&quot

 

[vix666]

I now get another error saying "you tried to execute a query that doesnt include the specified expression 'Format([Date], ""mm"&quot as part of an aggregate function.

Any ideas?

Thanks for all the help

Vicky

 

[nicsin]

Did you use the alias, ie "month" or 'Format([Date], ""mm"&quot at the GROUP BY clause? Try using the other instead.

 

[vix666]

I thought i'd already tried that but I tried it again and it stopped erroring, now i get another error
It says "too few parameters expected 3", the code is below


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 [Type of Complaint], Month; "

 

[nicsin]

Insert # before and after dates like:

between #date1# and #date2#