Fiscal Quartly now report **Help**

[rbellucci]

Ok stupid question, I have done this before without the now function and it seems to be kicking my butt,

SELECT IRTSI.EIR_status, Count(IRTSI.EIR_status) AS CountOfAction
FROM IRTSI
GROUP BY IRTSI.EIR_status, Year([irtsi].[incident_Date]), DatePart("q",Date())
HAVING (((Count(IRTSI.EIR_status))>0) AND ((Year([irtsi].[incident_Date]))=Year(Now())) AND ((DatePart("q",Date()))=DatePart("q",Now())))
ORDER BY IRTSI.EIR_status
WITH OWNERACCESS OPTION;



^current code, I need it to produce fiscal quarters instead of jan-dec



my fiscal quarters go from Oct 1, till sept. Any ideas?

[June7]

Shouldn't the expression use Incident_Date, not Date?

Calculate alternate quarter ID:

Switch(Month([Incident_Date])<=3,2, Month([Incident_Date])<=6,3, Month([Incident_Date])<=9,4, Month([Incident_Date])<=12,1)

[rbellucci]

Sorry I've been on leave for the last couple of days, the current code is presenting the user with live current data for the quarter in the form, as far as the switch where would i input that in, i'm getting an syntex error.

[June7]

Sorry, missing the second paren for each Month function. I have edited the post to correct.

Use that expression in a query to construct a field.

Can apply filter and/or sort criteria to that constructed field.

[rbellucci]

Sorry, missing the second paren for each Month function. I have edited the post to correct.

Use that expression in a query to construct a field.

Can apply filter and/or sort criteria to that constructed field.

Maybe i'm missing something here, sorry once again just got back from leave (Bear season in New England), I adjusted the "date" to incident_date (fixed the issue of reporting to many numbers for the current quarter). as far as switching where should I input that in? I put that at the end of the query and I get numbers counted 3 times, like 33, 33, 33, 33 so replicated data.

[June7]

Use the Switch expression in place of each DatePart() expression.

GROUP BY EIR_status, Year([incident_Date]), Switch(Month([Incident_Date])<=3,2, Month([Incident_Date])<=6,3, Month([Incident_Date])<=9,4, Month([Incident_Date])<=12,1) HAVING Count(EIR_status)>0 AND Year([incident_Date])=Year(Now()) AND Switch(Month([Incident_Date])<=3,2, Month([Incident_Date])<=6,3, Month([Incident_Date])<=9,4, Month([Incident_Date])<=12,1) = Switch(Month(Date())<=3,2, Month(Date())<=6,3, Month(Date())<=9,4, Month(Date())<=12,1)