Here is a doozey....

[Deirdre Marie]

..please bear with me.

I have created a database for storing injury and reaction information. The form and queries and reports are functioning just fine. My organization's fiscal year starts on July 01 and ends on Jun 30. Alexandre was wonderful to show me how to change the "Quarter" designation to reflect my organization's fiscal year on reports, and not the built-in Calendar year.

Since this database will eventually be extensive and include many years, I will need to make it so the user can select a quarter AND a year to print information for a report.

Using a simple Between/And statement would of course work, however I am not confident the users will even know when a fiscal year starts much less when a quarter starts and ends. They DO know which quarter they want though (Just not the months said quarter encompasses)Therefore, what I would *LIKE* to do, is prompt the user to enter a "Quarter Number" 1-4 and then the Year in which they wish a report of information.

I think what I will have to do is define on the form just exactly what Q1 is (July*-Sept*)since there will be many years. So if Date of Discovery = August 5, that will be Q1 etc etc. The trouble is, I simply do not know how to implement this.

Your patience and suggestions are very much appreciated.

~Deirdre

 

[pcs]

could you make a small table like:
Q1 StartDte EndDte
Q2 StartDte EndDte
etc.

use this as the source for a list box on the form that calls your report. add a textbox for the year. concatenate the two, and call the report using these Between dates in the query for the report.

hth,
al

 

[Alexandre]

_______________________
Alexandre was wonderful

_______________________

This and the Snoopy dance, how could I resist?

So here is my proposal

You have a form,
-a combo box: ChosenQuarter, which Row Source Type is a Value List, and Row Source: 1;"Q1";2;"Q2";3;"Q3";4;"Q4"
-a text box: ChosenYear

-a command button to launch your report. In the After_Update event of the command button, the following code:

Const CorrectionFactor = 2
'Your fiscal quarters = ´normal´ quarters + 2
'I put it in a constant just to show clearly how you would get ´regular´ quarters
Dim vStartDateFiscalQuarter As Variant
Dim vEndDateFiscalQuarter As Variant
Dim strWhere

'We build the first-day-of-the-quarter date from user's input
vStartDateFiscalQuarter = DateSerial(Me.ChosenYear, (Me.ChosenQuarter + _ CorrectionFactor - 1) * 3 + 1, 1)
'We deduce the last-day-of-the-quarter
vEndDateFiscalQuarter = DateAdd("m", 3, vStartDateFiscalQuarter) - 1
'We make sure dates are in the US mm/dd/yyyy format whatever the regional
'settings may be and, we format them into a ready-to-use-in-SQL string
Let vStartDateFiscalQuarter = Format$(vStartDateFiscalQuarter, _ "\#mm\/dd\/yyyy\#;;;\N\u\l\l")
Let vEndDateFiscalQuarter = Format$(vEndDateFiscalQuarter, _ "\#mm\/dd\/yyyy\#;;;\N\u\l\l")

'strWhere is the string holding your filter criteria. We add it the new criteria
strWhere = strWhere & " Between " & vStartDateFiscalQuarter & _
" And " & vEndDateFiscalQuarter

'And launch the report
DoCmd.OpenReport HereIsYourReportNameAsString, acViewPreview, , strWhere


C'mon, did I win a Mega-Snoopy dance?
Did I?

BTW, what is a doozey?

Alex

[This message has been edited by Alexandre (edited 09-20-2001).]

 

[Deirdre Marie]

Pcs:

Thank you for taking the time to assist me in this endeavour. Unfortunately I am a beginner to ACCESS and will need more time to 'play' with your method.

Alexandre:
I've tried following your 'recipe' and have come to a stumbling point. I created a form per your instruction, however when I created the "command" button, I did not receive an "After Update" option in its properties. It seems "After Update" is not for command buttons, but more for boxes.

Am I doing something incorrectly?

And to answer your question:
doo·zy or doo·zie (dz)
n. Slang pl. doo·zies

Something extraordinary or bizarre

:: smiles ::

 

[Alexandre]

Deirdre Marie

Verrrrry sorry. It was yet pretty late at night when I sent the post...
I meant the On_click event of course. Command buttons have no After_update event: you just click them to trigger the command. Only controls that let you enter/update values have an After_update event.

Alex

[This message has been edited by Alexandre (edited 09-20-2001).]

 

[Deirdre Marie]

:: smile ::

Alexandre,

I sent you an email at the email address you have listed in your profile.

~Deirdre