Need help with Month function

[jenkinsloveschicken]

Could someone please take a look at my query and tell me what I am missing? The strange thing is that when I first built the query, it was working fine. Then it broke. I suspect the problem is the Call Date field. Without the Call Date parameter the query runs fine. However I need the Call Date field so that I can specify the Month function to pull the cumulative data for month-to-date based on the current month(Unless someone else has a better method of doing this). I double checked my data types for the Call Date field and it is the same as when I first built the query. The goal is to calculate averages from the fields listed below and group by the line of business. At any rate, here is the query:

Avg([Centerwide CSAT].[Spnt Sat]) AS Spnt_Top_2 : Returns an average rating for company brand satisfaction

Avg([Centerwide CSAT].[Specialist Sat]) AS Specialist_Top_ 2 : Returns an average rating for specialist satisfaction

Avg(IIf([Centerwide CSAT].[Issue Rslv]<>'NA',[Centerwide CSAT].[Issue Rslv])) AS Issue_Resolved_ Average : Returns an average value for Issue Resolved rankings excluding values of N/A

[Center Headcount].Account : Returns the line of business


SELECT Avg([Centerwide CSAT].[Spnt Sat]) AS Spnt_Top_2, Avg([Centerwide CSAT].[Specialist Sat]) AS Specialist_Top_ 2, Avg(IIf([Centerwide CSAT].[Issue Rslv]<>'NA',[Centerwide CSAT].[Issue Rslv])) AS Issue_Resolved_ Average, [Center Headcount].Account
FROM [Centerwide CSAT] INNER JOIN [Center Headcount] ON [Centerwide CSAT].[Specialist Name] = [Center Headcount].Name
GROUP BY [Center Headcount].Account, [Centerwide CSAT].[Call Date]
HAVING ((([Centerwide CSAT].[Call Date])=(Month(Now()) )));


Thanks,
Gerald

[Killer42]

That's a very clear and detailed problem description, which is always a nice thing to see. However, could you please provide further detail on how it "broke"?

[ADezii]

Could someone please take a look at my query and tell me what I am missing? The strange thing is that when I first built the query, it was working fine. Then it broke. I suspect the problem is the Call Date field. Without the Call Date parameter the query runs fine. However I need the Call Date field so that I can specify the Month function to pull the cumulative data for month-to-date based on the current month(Unless someone else has a better method of doing this). I double checked my data types for the Call Date field and it is the same as when I first built the query. The goal is to calculate averages from the fields listed below and group by the line of business. At any rate, here is the query:

Avg([Centerwide CSAT].[Spnt Sat]) AS Spnt_Top_2 : Returns an average rating for company brand satisfaction

Avg([Centerwide CSAT].[Specialist Sat]) AS Specialist_Top_ 2 : Returns an average rating for specialist satisfaction

Avg(IIf([Centerwide CSAT].[Issue Rslv]<>'NA',[Centerwide CSAT].[Issue Rslv])) AS Issue_Resolved_ Average : Returns an average value for Issue Resolved rankings excluding values of N/A

[Center Headcount].Account : Returns the line of business


SELECT Avg([Centerwide CSAT].[Spnt Sat]) AS Spnt_Top_2, Avg([Centerwide CSAT].[Specialist Sat]) AS Specialist_Top_ 2, Avg(IIf([Centerwide CSAT].[Issue Rslv]<>'NA',[Centerwide CSAT].[Issue Rslv])) AS Issue_Resolved_ Average, [Center Headcount].Account
FROM [Centerwide CSAT] INNER JOIN [Center Headcount] ON [Centerwide CSAT].[Specialist Name] = [Center Headcount].Name
GROUP BY [Center Headcount].Account, [Centerwide CSAT].[Call Date]
HAVING ((([Centerwide CSAT].[Call Date])=(Month(Now()) )));


Thanks,
Gerald

It seems to me as though in your HAVING clause, you are trying to equate a Date Field [Call Date], to an Integer value Month(Now()) returned from the Month() Function. Have you tried this in the HAVING clause instead?

HAVING (((Month([Centerwide CSAT].[Call Date]))=(Month(Now()))));

[Killer42]

It seems to me as though in your HAVING clause, you are trying to equate a Date Field ... to an Integer value Month(Now()) ...

Good catch! I missed that.

Still too many parentheses though, in my opinion. That's not a bug or anything, I just hate the way Access throws in great long unnecessary strings of the stupid things.

[jenkinsloveschicken]

I am unsure of how it broke. I initially had problems with the Call Date parameter resulting in no records. So I played with the data type(original was Date/Time and I had changed it to Text) for the call date to see if it was a reference problem with the Month function. With the data type as Text, it results in inaccurate results, but Date/Time datatype is accurate in the report output. To be honest, I don't completely understand the all of the date functions in Access.

The query minus Call Date will run all of the appropriate averages for each line of business. However, the output is to a subform on one of the Main forms in the database and I would like it to always pull the averages based on month-to-date information in the underlying table. Is there an easier or more effective way to do this?

Also, I'm kinda lost with the original reply:
It seems to me as though in your HAVING clause, you are trying to equate a Date Field [Call Date], to an Integer value Month(Now()) returned from the Month() Function. Have you tried this in the HAVING clause instead?

What is meant by: Have you tried this in the HAVING clause instead? Do I need to manipulate the HAVING clause itself? Or change the datatype for the Call Date field to something else?

Thanks for the help, as it is much appreciated!

Regards,
Gerald

[jenkinsloveschicken]

Sorry, I just went back and re-read the original reply and noticed the difference in the code. Thank you, I will try that and let you know if it works! Again, thanks for the help!