Grouping data into Quarters using IIF expression

[DrDoIT]

Hello guys,

I have written the following expression in a Ms Querry. I have a table called 711PMTCTData which has a Look up field called MonthReported with Text as data type. the data entered in that field are the abbreviations (Jan, Feb, Mar upto Dec). I would like the expression to return in a calcuated field the quarters Q1,Q2,Q3,Q4; but the expression below only returns Q1 irrespective of the month entered.

Qtr Reported: IIf([711PMTCTData]![MonthReported]="Oct" Or "Nov" Or "Dec","Q1",IIf([711PMTCTData]![MonthReported]="Jan" Or "Feb" Or "Mar","Q2",IIf([711PMTCTData]![MonthReported]="Apr" Or "May" Or "Jun","Q3","Q4")))

a).Would it make a difference if i used Date instead of Text as the data type in the field called MonthReported?
b).How should the expression be written?

Kindly assist.
Thank you.

 

[speakers_86]

which has a Look up field called MonthReported with Text as data type.

Don't use a lookupfield. There are plenty of posts on this forum about it, just search if you want to know why they are bad.

a).Would it make a difference if i used Date instead of Text as the data type in the field called MonthReported?

I don't know, but the function Cdate(YourDate) converts text to dates. You can play with that.

edit- Based on how your query works it shouldn't matter.
edit-CDate may not work for your format though.

b).How should the expression be written?

I don't see anything wrong with your query. Try to dumb it down to narrow down the problem. Only use one iif statement, see if it works as expected. Then two, then three.

I'd be better with vba as opposed to a query, plus this is probably better on a form anyway.

Dim Quarter as string
Select Case Me.MonthReported
    Case "Jan"
          Quarter="Whatever quarter you call january"
    Case "Feb"
        Quarter="Whatever quarter you call february"
    .
    .
    .
    Case "Dec"
        quarter="Whatever quarter you call december"
    Case Else
        Msgbox "Error- Unhandled case"
End Select

 

[DrDoIT]

Though i am just beginning out on VBA, i will try it out.
Thank you.

 

[jdraw]

You might wish to consider Datepart() which can return the Quarter for any given Date.

see http://www.techonthenet.com/access/functions/date/datepart.php

 

[stopher]

You might wish to consider Datepart() which can return the Quarter for any given Date.

see http://www.techonthenet.com/access/functions/date/datepart.php

The problem is that quarter 1 starts in October whereas Datepart assume Q1 starts in Jan. You could use the MOD function to shift the quarter produced by Datepart.

Another approach is to create a table containing 12 records (one for each month). Each record has MonthDesc and the corresponding Quarter. Then just use this table in a join query.

hth
Chris

 

[speakers_86]

I don't see any reason to do that when a simple select case will do it.

edit-The format of his 'date' complicates using datepart or similar functions too. In the immediate window, I tried to see if I could get "Jan" to return a quarter, and the best I could get was a type mismatch.

edit- I fixed your sql. Just edit the quarters to suite your needs. Give this a try:

SELECT [711PMTCTData].MonthReported, IIf([monthreported]="Jan" Or [monthreported]="Feb" Or [monthreported]="Mar","Qtr1",IIf([monthreported]="Apr" Or [monthreported]="May" Or [monthreported]="Jun","Qtr2",IIf([monthreported]="Jul" Or [monthreported]="Aug" Or [monthreported]="Sept","Qtr3","Qtr4"))) AS Qtr
FROM 711PMTCTData;

 

[DrDoIT]

Thanks all for the suggestions. seperating the months as follows sorted the problem :

Qtr: IIf([711PMTCTData]![MonthReported]="Oct","Q1",IIf([711PMTCTData]![MonthReported]="Nov","Q1",IIf([711PMTCTData]![MonthReported]="Dec","Q1",IIf([711PMTCTData]![MonthReported]="Jan","Q2",IIf([711PMTCTData]![MonthReported]="Feb","Q2",IIf([711PMTCTData]![MonthReported]="Mar","Q2",IIf([711PMTCTData]![MonthReported]="Apr","Q3",IIf([711PMTCTData]![MonthReported]="May","Q3",IIf([711PMTCTData]![MonthReported]="Jun","Q3","Q4")))))))))

As you can see i ended nesting up to 9 IIf expressions.

Immediately after that i ran into further problems - the data needed to be grouped by project years - since it is a 5 year project multiplying that by 12 months gives 60 possible entries. Using IIF expressions for that proved too complex.

VBA is quite intimidating for beginners like me, but I have realised that I can't avoid it forever. I will try writing the code in VBA and post it to the forum for debugging.

Thanks,

DrDoIT.

 

[speakers_86]

IIf statements can only be nested so many times. I think I thought the number was seven, but if you did nine, I guess it's nine. Did you seem my last post? I fixed your original sql. No need to break out the months.

I don't think I understand this business of 60 entries. However, you don't have to do everything in one query.

 

[stopher]

Here's a simpler expression to give you the quarter:

Qtr: "Q" & Round((Month(DateValue([MonthReported] & " 1,2000"))+1)/3,0)

What I've done here is use the DateValue and Month functions to convert a month description to a month number. Then by adding 1 and dividing by 3 we get the shifted quarter number.

hth
Chris

Edit: corrected formula

 

[speakers_86]

I like it, good job stopher.

 

[Brianwarnock]

Here's a simpler expression to give you the quarter:

Qtr: Round(Month(DateValue([MonthReported] & " 1,2000"))/3,0)

What I've done here is use the DateValue and Month functions to convert a month description to a month number. Then by adding 1 and dividing by 3 we get the shifted quarter number.

hth
Chris

It must be my old machine because it doesn't work for me, in fact it returned 0 for January, 3 for Decemberand others were out by -1, tweaking it so
Round(Month(DateValue([MonthReported] & " 1,2000"))/3+0.4,0)

gave the correct qtr results but not shifted as per required.

I think Chris's idea of a table is the one to go for. mind you we need an answer to the 60 options mentioned.

Brian

 

[MSAccessRookie]

Hello guys,

I have written the following expression in a Ms Querry. I have a table called 711PMTCTData which has a Look up field called MonthReported with Text as data type. the data entered in that field are the abbreviations (Jan, Feb, Mar upto Dec). I would like the expression to return in a calcuated field the quarters Q1,Q2,Q3,Q4; but the expression below only returns Q1 irrespective of the month entered.

Qtr Reported: IIf([711PMTCTData]![MonthReported]="Oct" Or "Nov" Or "Dec","Q1",IIf([711PMTCTData]![MonthReported]="Jan" Or "Feb" Or "Mar","Q2",IIf([711PMTCTData]![MonthReported]="Apr" Or "May" Or "Jun","Q3","Q4")))

a).Would it make a difference if i used Date instead of Text as the data type in the field called MonthReported?
b).How should the expression be written?

Kindly assist.
Thank you.

IIF does not work that way, but you can group them and use IN:

Qtr Reported: IIf([711PMTCTData]![MonthReported] IN ("Oct", "Nov", "Dec"), "Q1", IIf([711PMTCTData]![MonthReported] IN ("Jan", "Feb", "Mar"), "Q2", IIf([711PMTCTData]![MonthReported] IN ("Apr", "May", "Jun"), "Q3", "Q4")))

 

[stopher]

It must be my old machine because it doesn't work for me, in fact it returned 0 for January, 3 for Decemberand others were out by -1, tweaking it so
Round(Month(DateValue([MonthReported] & " 1,2000"))/3+0.4,0)

gave the correct qtr results but not shifted as per required.

Thanks Brian. I think your old machine is fine. I typed the formula wrong. Too much Christmas spirit. I've corrected it now.

Chris

 

[DrDoIT]

IIF does not work that way, but you can group them and use IN:

Qtr Reported: IIf([711PMTCTData]![MonthReported] IN ("Oct", "Nov", "Dec"), "Q1", IIf([711PMTCTData]![MonthReported] IN ("Jan", "Feb", "Mar"), "Q2", IIf([711PMTCTData]![MonthReported] IN ("Apr", "May", "Jun"), "Q3", "Q4")))

Grouping the months using IN has worked very well for me.

Next moving on to try the VBA ones posted below.

Thanks all for stepping in.

DrDoIT