IIf And expression not quite working

[Ted M H]

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?

 

[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

 

[Douglas J. Steele]

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

 

[Ted M H]

Many thanks for the very prompt, very helpful response.

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


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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?

 

[Rob]

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

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