IF statement to display four possibilities

[CharlieT302]

Hi,

In a query criteria, I need an If statement to display four possibilities. Can this be done?

What I am doing is trying to display quarters for a fiscal year. For example:

July - Sept is Qtr 1
Oct - Dec is Qtr 2
Jan - Mar is Qtr 3
Apr - June is Qtr 4

I need my statement to say If current month is July then display 1.

Thanks

 

[RuralGuy]

Hi Charlie,
Hopefully your field is a date field. A fiscal year is simply the current date plus six months in your case. Then FiscalQuarter:=Format(DateAdd("m",+6,[YourDate]),"q") should give you the integer value of the proper fiscal quarter.

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[PHV]

Something like this ?
Format(Date(),"q")+2*(Format(Date(),"q")>2)-2*(Format(Date(),"q")<3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

Hi RuralGuy,

Actually, it is not a date field. Here is what am now facing.

The field in question is reporting quarters 1,2,3, and 4 as numbers. So, what I need is to convert 3 (actual 3rd qtr) into 1 (fiscal qtr).

I should have been clearer at first

 

[PHV]

Perhaps an expression like this ?
FiscalQuarter: Actualquarter+2*(Actualquarter>2)-2*(Actualquarter<3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[RuralGuy]

You can see that this field is currently pretty limited and would be much more flexible as a date. As for your problem, try:
FiscalQuarter:=Choose([YourField],3,4,1,2)

HTH RuralGuy (RG for short) acXP winXP Pro
Please respond to this forum so all may benefit

 

[CharlieT302]

Unfortunately, I have to live with the field as is. However, I think I found a much simpler solution. PHV gave me the idea.

Fiscal_Qrt: IIf([pqrt]<=2,[pqrt]+2,[pqrt]-2)

Therefore:
July - Sept is Qtr 3 Becomes 1
Oct - Dec is Qtr 4 Becomes 2
Jan - Mar is Qtr 1 Becomes 3
Apr - June is Qtr 2 Becomes 4