IIf And expression not quite working

G

Guest

I am trying to determine the quarter of a ship date using an expression:

Expr1: IIf(Month([PaymentsDetails]![ShipDate]>=1) And
Month([PaymentsDetails]![ShipDate]<=3),"Q1","QY")

I'll add the other nested IIfs later, but right now I'm just trying to get
the expression above to work. It's currently returning Q1 for any value
other than null.

12/1/2004 is Q1
1/26/2005 is Q1
null is QY

Somehow the expression isn't doing the And operator. What am I doing wrong
here?
 
B

Bob Quintal

I am trying to determine the quarter of a ship date using an
expression:

Expr1: IIf(Month([PaymentsDetails]![ShipDate]>=1) And
Month([PaymentsDetails]![ShipDate]<=3),"Q1","QY")

I'll add the other nested IIfs later, but right now I'm just
trying to get the expression above to work. It's currently
returning Q1 for any value other than null.

12/1/2004 is Q1
1/26/2005 is Q1
null is QY

Somehow the expression isn't doing the And operator. What am
I doing wrong here?
Why not approach this from a different perspective:
Expr1: int((month([shipdate])-1)/4) +1
 
D

Douglas J. Steele

Expr1: IIf((Month([PaymentsDetails]![ShipDate])>=1) And
(Month([PaymentsDetails]![ShipDate])<=3),"Q1","QY")
 
G

Guest

Many thanks for the very prompt, very helpful response.

Douglas J. Steele said:
Expr1: IIf((Month([PaymentsDetails]![ShipDate])>=1) And
(Month([PaymentsDetails]![ShipDate])<=3),"Q1","QY")


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ted M H said:
I am trying to determine the quarter of a ship date using an expression:

Expr1: IIf(Month([PaymentsDetails]![ShipDate]>=1) And
Month([PaymentsDetails]![ShipDate]<=3),"Q1","QY")

I'll add the other nested IIfs later, but right now I'm just trying to get
the expression above to work. It's currently returning Q1 for any value
other than null.

12/1/2004 is Q1
1/26/2005 is Q1
null is QY

Somehow the expression isn't doing the And operator. What am I doing
wrong
here?
 
G

Guest

Another option is to write a function that you can use from anywhere:

Function CheckQuarter(dteDate)

ShipMonth = Month(dteDate)
MonthsPerPeriod = 3
M1 = (Round(ShipMonth / MonthsPerPeriod, 3)) * MonthsPerPeriod
M2 = (Round(ShipMonth / MonthsPerPeriod, 0)) * MonthsPerPeriod
Quarter = M2
If M1 > M2 Then Quarter = M2 + MonthsPerPeriod
Quarter = Quarter / MonthsPerPeriod
CheckQuarter = Quarter

End Function


Bob Quintal said:
I am trying to determine the quarter of a ship date using an
expression:

Expr1: IIf(Month([PaymentsDetails]![ShipDate]>=1) And
Month([PaymentsDetails]![ShipDate]<=3),"Q1","QY")

I'll add the other nested IIfs later, but right now I'm just
trying to get the expression above to work. It's currently
returning Q1 for any value other than null.

12/1/2004 is Q1
1/26/2005 is Q1
null is QY

Somehow the expression isn't doing the And operator. What am
I doing wrong here?
Why not approach this from a different perspective:
Expr1: int((month([shipdate])-1)/4) +1
 

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