Case or IIF?

[ckrows]

I want to show certain text depending on the date/date range.

Example if date is between 1/1/08 and 3/31/08, text on a report would reflect as "First Quarter"
4/1/08 - 6/30/08 would show text Second Quarter.

I tried using >=, i tried building into the query as a temp field, but it does not seem to work like i thought it would.

'If [quarter] >= "01/01/2008" or =< "03/31/2008" Then
'Me.[Quarter] = "First Quarter"

I wasn't sure if to use the Case function or an IIF statement...

suggestions.

[Scott Price]

Be aware that the DatePart() function includes the ability to tell which quarter the passed in date belongs to...

For example:

DatePart("q", Now())

will return the current quarter of the current year. You can replace Now() with any valid date format, including a date string (enclosed in # marks, i.e. #1/1/2008#).

You can then use this numbered value to determine what text you wish to show on the report... For example, in the OnOpen event of your report, you can place code something like this:

If DatePart("q", [PassedInDate]) = 1 Then
     Me.Label1.Caption = "First Quarter Results"
ElseIf DatePart("q", [PassedInDate]) = 2 Then
     Me.Label1.Caption = "Second Quarter Results"
'etc etc etc
End If

Kind Regards,
Scott

[Stewart Ross]

... I tried using >=, i tried building into the query as a temp field, but it does not seem to work like i thought it would.
'If [quarter] >= "01/01/2008" or =< "03/31/2008" Then
'Me.[Quarter] = "First Quarter"...

Hi. A number of contributors find dates a bit difficult to manipulate, for similar reasons. Your query is not actually comparing dates to dates, but text values which look like dates to dates. To get you going a bit change "01/01/2008" and "03/31/2008" in your query to #01/01/2008# and #03/31/2008#. The hash marks are Access's syntax for defining within queries and in direct passing of values to parameters that the characters that follow to the closing hash are dates.

I think this will not take you very far, and you need to give some further thought to what you are wishing to achieve. If you wish to filter a report you will need changeable values to compare the rows against, and these will have to be provided by the user using a text box or combo box to enter or select date values. Comparing text box values to dates requires conversion to an actual date using CDate(), say.

If you want to select reports for given quarters of the year you can calculate the report quarter and year using a summary query on the dates in your table. Assuming that the underlying date values are really dates, and not text strings, you can use Year([name of date field]) to return the current year, and Month([name of month field]) to return the current month. The quarter can be calculated from the month as
Int(Month([name of month field]) / 4) + 1.

The calculated Year and Quarter fields can be used in a totals query to be the source for a selection of the specific periods to be reported. I suspect this would be a fair amount of work for you to achieve at this stage, though!

Hope your developments progress

-Stewart

[Scott Price]

Hi Stewart,

Thanks for your input. I'm just wondering about calculating the quarter the way you have done it, while it's a valid way of arriving at the quarter, why not just use the DatePart("q", [Date]) method, which is easier and works just as well in queries as in VBA?

Anyway we can wait till we find which way the OP wishes to go :-)

Regards,
Scott

[jaxjagfan]

I use Scott's method but try to avoid writing any VBA if possible.

Select "Quarter " & DatePart("q", tblMyData.MyDate) as Qtr, Sum (tblMyData.MyValue) as TotVal
From tblMyData
Group By DatePart("q", tblMyData.MyDate)
 

This will change your text to "Quater 1" but no iif's or case's required. It will also group and sum the data to the Quarter level.

[Stewart Ross]

I use Scott's method but try to avoid writing any VBA if possible.

Thanks Scott and jaxjagfan for the tips. When I mentioned the quarter calculation (however its done - and athough I have used date functions for many years this is the first time I have come across the datepart function, which seems better to me too!) it was not for VBA coding, but for direct inclusion as a calculated field in the grouping query, just as jaxjagfan notes. I, too, try wherever possible to use queries to do the work. VBA is hidden away from view, making it more difficult for a user to understand what a query is doing if a lot of its processing is done by VBA instead of the SQL.

Looking ahead to what CKRows wants to do with the data once filtered is where I think there could be a need for some VBA, if only to apply the same filtering to the underlying report.

This is a great forum to contribute to - keep up the good work!

-Stewart