IIF/AND statement help

[Pat Dools]

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

 

[nomadk]

I think you could use:

Switch([Quote_Mnth Between 1 and 3,"Q1",[Quote_Mnth] Between 4 and
6,"Q2",[Quote_Mnth] Between 7 and 9,"Q3",[Quote_Mnth] Between 10 and 12,"Q4")

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

 

[KARL DEWEY]

Use the date function ---- Format([Quote_Mnth],"q")
It works quite well.
--
KARL DEWEY
Build a little - Test a little

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

 

[nomadk]

Cool Karl - I had no idea.

Use the date function ---- Format([Quote_Mnth],"q")
It works quite well.
--
KARL DEWEY
Build a little - Test a little

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

 

[John W. Vinson]

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

If Quote_Mnth is a date field, the Format() function suggested elsethread
should work; if it's an integer 1 to 12, try some simple arithmatic:

(([Quote_mnth] - 1)\3)+1

This subtracts one to get 0, 1, 2 for the first quarter; the integer divide
operator maps 0, 1, 2 to 0, 3, 4, 5 to 1 etc; adding one gets back to quarters
1, 2, 3 and 4.

 

[Pat Dools]

Hi John,

This is awesome to use when you only have 'Month Number' instead of an
actual date field to work with, as is the case w/ this report. Thanks!
--
Pat Dools

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!

If Quote_Mnth is a date field, the Format() function suggested elsethread
should work; if it's an integer 1 to 12, try some simple arithmatic:

(([Quote_mnth] - 1)\3)+1

This subtracts one to get 0, 1, 2 for the first quarter; the integer divide
operator maps 0, 1, 2 to 0, 3, 4, 5 to 1 etc; adding one gets back to quarters
1, 2, 3 and 4.

 

[Pat Dools]

Hi Karl,

A very simple formula when you have an actual date field to work with.
Thanks!
--
Pat Dools

Use the date function ---- Format([Quote_Mnth],"q")
It works quite well.
--
KARL DEWEY
Build a little - Test a little

Hello,

I am trying to assign 'Quarter' values based on a month number with the
following formula. It works great for 'Q1' and 'Q2', but does not work
(leaves blanks) when the month number falls within the third ('Q3') or fourth
quarter ('Q4'):

Qt_Qtr:
IIf([Quote_Mnth]<4,"Q1",IIf([Quote_Mnth]>3,IIf([Quote_Mnth]<7,"Q2"),IIf([Quote_Mnth]>6,IIf([Quote_Mnth]<10,"Q3"),"Q4")))

If anyone has easier ways of assigning a 'Quarter' value to a record, I'm
also open to any suggestions!