# IIF/AND statement help

P

#### 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!

N

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")

Pat Dools said:
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!

K

#### KARL DEWEY

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

Pat Dools said:
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!

N

Cool Karl - I had no idea.

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

Pat Dools said:
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!

J

#### 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.

P

#### 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

John W. Vinson said:
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.

P

#### Pat Dools

Hi Karl,

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

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

Pat Dools said:
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!