Previous quarter

[Tony Williams]

I have a form which is used to determine the date parameters for a report.
The user has to select the current quarter from a combo list, which is based
on a table called tblmonth and then select the previous quarter from a
second combo box, also based on tblmonth. The quarters are March, June,
September and December. I would like the user to only have to select the
current quarter and for Access to automatically calculate the previous
quarter, bearing in mind that the previous quarter for say March 2008 would
be December 2007.
Is this possible and if so how do I do it? I assumed I would create an
invisible control on the form which was a calculated control but I'm not
sure what the formula would be.
Thanks
Tony

 

[Mr B]

Tony,

I am having to assume from your description that you only have the months of
March, June, September, and December in your tblMonth table.

For my testing here, I used the following:

A combo box which I named: "cboCurQtr" with the "Row Source Type" property
set to "Value List", the "Row Source" property set to
"March;June;September;December"

A text box (mine was visible but yours could be hidden) which I named:
"txtPrevQtr"

With these controls on my form, I then used the following code in the "After
Update" event of the "cboCurQtr" combo box control:

'code below this line
Dim strCurYr As String
Dim strPrvYr As String
strCurYr = Str(Year(Date))
strPrvYr = Str(Year(Date)) - 1

Select Case Me.cboCurQtr
Case "March"
Me.txtPrevQtr = "December " & strPrvYr
Case "June"
Me.txtPrevQtr = "March " & strCurYr
Case "September"
Me.txtPrevQtr = "June " & strCurYr
Case "December"
Me.txtPrevQtr = "September " & strCurYr
End Select
'end of code

When a selection is made from the combo box, the appropriate value will be
written to the text box. You indicated that you would want the Year to be
included in the text box. If this is not the case, you could simply remove
the code where I concatenate the currrent year or the previous year to the
string that is to be written to the text box.

I am not exactly sure what you then plan to do with the value that will be
written to the text box, but I do hope this will help you.

 

[Tony Williams]

Thanks Mr B I'll study that and see how it fits what I want to do.
Thanks again
Tony

 

[Mr B]

Tony,

You are welcome. If you still need assistance, please just post back here
and someone will try to assist.

Mr B
askdoctoraccess dot com