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

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

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

nomadk

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top