Date Range spans 2 yrs

[Fizzio]

I have a reports menu that can churn out reports for several time scales eg months, quarters , calendar year and financial year. I use 2 combo boxes. 1 to select the timescale, 1 to select the year. The timescale combo looks up date values from a lookup table ie StartDate and EndDate. These are formatted dd/MM.
In my query I have the criteria for DateofContact to Between [StartDate] and [EndDate] and the year set to the year combo. I use the same query for all the reports so how do I alter my query so that it can span 2 years eg for financial year queries? The SQL looks like

PARAMETERS [Forms]![ReportsMenu]![cboSelTime] Long;
SELECT DISTINCT PatientInfo.PatientGender, AgeBands.AgeBandDesc, Count(Contacts.ContactID) AS CountOfContactID
FROM TimeScale, ((AgeBands INNER JOIN PatientInfo ON AgeBands.AgeBandID = PatientInfo.AgeBandID) INNER JOIN ReferralInfo ON PatientInfo.PatientID = ReferralInfo.PatientID) INNER JOIN (ContactTypes INNER JOIN Contacts ON ContactTypes.ContactTypeID = Contacts.ContactTypeID) ON ReferralInfo.ReferralID = Contacts.ReferralID
WHERE (((ContactTypes.ContactType)="First In Financial Year") AND ((TimeScale.TimeScaleID)=[Forms]![ReportsMenu]![cboSelTime]) AND ((Contacts.DateOfContact) Between Format([TimeScale]![TimeScaleStart],"dd\/mm") And Format([TimeScale]![TimeScaleEnd],"dd\/mm")) AND ((Format([DateOfContact],"yyyy"))=[Forms]![ReportsMenu]![cboYearSelect]))
GROUP BY PatientInfo.PatientGender, AgeBands.AgeBandDesc;

It works fine for separate months etc but I cannot suss out how to adjust it to be able to handle Q4 and Financial Year??

 

[Fizzio]

C'mon guys give me a hand on this one!
I essentially need a function/criteria expression to select a financial year from a date field in a query ie Between 1/4/02 And 31/3/03 etc, except I need to be able to choose the year (which I do via a form) so how do I plug it into the query?

 

[ColinEssex]

The way I did it (and I know you shouldn't store unnecessary data) is to create new fields in the table that holds the date you need to search on.
These new fields can be then updated to
1) Quarters. (Q1 Q2 etc)
and
2) Financial year ( 01/02 or 02/03 etc)

What I did then was to use those fields in the query with parameters. The quarters parameter can then have a wildcard * so that you can either
[select quarter, or leave blank for all] and
in the other field [select financial year]

So you have to select a Fin Year but have a choice over the quarter you want

There's got to be a more efficient way, but this does work ok.

Hope this helps you get started, perhaps someone else will add to it.

Col

 

[Fizzio]

Cheers for the reply Col. I currently use a lookup table with timescales for months, quarters and fin year. I select the timescale and year from 2 dropdown boxes and use these to populate the criteria. This works OK until I choose fin year because it spans 2 years! I wnat to be able to use a) the same query to b) populate the same report to give universality. Am I asking too much?? or do I have to create a new query then change the recordsource by code.

Thanks again.

Kristin

 

[Fizzio]

Anyone who's interested, I found a workaround. I set up 2 invisible fields on the form and via code set the start and end dates using an if..then. I then used these for the parameters in the query.