SQL error

[smithd14]

I have been trying to get this SQL string statement to run for some time now.

The current error is:
You tried to execute a query that does not include the specified expression 'Recruiter' as part of an aggregate function. (Error 3122)

I am assuming this is due to the code thinking 'recruiter' is a summed value. I am pretty new to SQL and Access VBA programming, so any help would be nice.

strSQLc = "SELECT [tbl Officer Prospecting Applicant Log].[Recruiter], " & _
         " Format([Contact Date],'mmm-yy') AS [Contact Month Range], " & _
         " Sum(IIf([Initial Contact] = 'Local Lead' Or [Initial Contact] = 'National Lead' Or [Initial Contact] = 'Phone Lead',1,0)) AS [Contact Leads] ," & _
         " Sum(IIf([Initial Contact]= 'Email' ,1,0)) AS [Contact Email] ," & _
         " Sum(IIf([Initial Contact] = 'Applicant Referral' Or [Initial Contact] = 'COI/Counselor' Or [Initial Contact] = 'Colliegate Referal' Or [Initial Contact] = 'Recuiter Referrral' Or [Initial Contact] = 'Referral Other Service' Or [Initial Contact] = 'Pro Navy',1,0)) AS [Contact Referrals] ," & _
         " Sum(IIf([Initial Contact] = 'Presentation/OHARP/SEMINAR',1,0)) AS [Contact Presentations] ," & _
         " Sum(IIf([Initial Contact] = 'Personally Developed Contact',1,0)) AS [Contact PDC] ," & _
         " Sum(IIf([Initial Contact] = 'Walk In',1,0)) AS [Contact Walk In/Mail Out] " & _
         " FROM [tbl Officer Prospecting Applicant Log] " & _
         " WHERE [Recruiter] = '" & str_recr & "' " & _
         " GROUP BY  Format([Contact Date],'mmm-yy')" & _
         " HAVING [Contact Date] = '" & mon1 & "' " & _
         " OR [Contact Date] = '" & mon2 & "' " & _
         " OR [Contact Date] = '" & mon3 & "'; "

 

[pbaldy]

Not really. Any field in the SELECT clause must either be part of an aggregate function or in the GROUP BY clause. What happens if you add that field to the GROUP BY clause?

 

[smithd14]

I took your advice and changed around the GROUP BY and HAVING functions. The SQL str will now create a QRY, but it doesn't seem to be grouping by the str_recr only mon1, mon2, mon3. What am I missing in the GroupBy statement?

strSQLc = "SELECT [tbl Officer Prospecting Applicant Log].[Recruiter], " & _
         " Format([Contact Date],'mmm-yy') AS [Contact Month], " & _
         " Sum(IIf([Initial Contact] = 'Local Lead' Or [Initial Contact] = 'National Lead' Or [Initial Contact] = 'Phone Lead',1,0)) AS [Contact Leads] ," & _
         " Sum(IIf([Initial Contact]= 'Email' ,1,0)) AS [Contact Email] ," & _
         " Sum(IIf([Initial Contact] = 'Applicant Referral' Or [Initial Contact] = 'COI/Counselor' Or [Initial Contact] = 'Colliegate Referal' Or [Initial Contact] = 'Recruiter Referrral' Or [Initial Contact] = 'Referral Other Service' Or [Initial Contact] = 'Pro Navy',1,0)) AS [Contact Referrals] ," & _
         " Sum(IIf([Initial Contact] = 'Presentation/OHARP/SEMINAR',1,0)) AS [Contact Presentations] ," & _
         " Sum(IIf([Initial Contact] = 'Personally Developed Contact',1,0)) AS [Contact PDC] ," & _
         " Sum(IIf([Initial Contact] = 'Walk In',1,0)) AS [Contact Walk In/Mail Out] " & _
         " FROM [tbl Officer Prospecting Applicant Log] " & _
         " GROUP BY [Recruiter], Format([Contact Date],'mmm-yy') " & _
         " HAVING [Recruiter] = '" & str_recr & "' " & _
         " AND Format([Contact Date],'mmm-yy') = '" & mon1 & "' " & _
         " OR Format([Contact Date],'mmm-yy') = '" & mon2 & "' " & _
         " OR Format([Contact Date],'mmm-yy') = '" & mon3 & "' ;"

 

[pbaldy]

If you're restricting to a single recruiter, I'm not sure what you would expect in the way of grouping. Can you post a sample db?

 

[RainLover]

What Paul said.

Post a sample.