IIf function, & Date(), & AND, & > & <

[Hurrikane4]

I'm trying to get the query to check if an anniversary date is within the
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")

 

[fredg]

I'm trying to get the query to check if an anniversary date is within the
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")

Using your Greater Than and Less Than approach:
Expr1:IIf(([Anniv] <=Date() + 30) And ([Anniv]>= Date()),"Yes","No")

Perhaps this approach might be simpler:
Expr1:IIf([Anniv] Between Date() and Date() + 30,"Yes","No")

 

[Phil Smith]

Don't add numbers to date fields. It can be problematic. Use DateAdd()
instead. It allows you to add days, months, whatever you want.

In your case:
IIf([Anniv]>Date() AND [Anniv]<DateAdd("d",30,date()), "Yes", "No")

OR, IIf([Anniv]>Date() AND [Anniv]<Dateadd("m",1,date()), "Yes",
"No")which would check for dates within a month, whether 30 days, 31 or 28.

Phil

 

[vanderghast]

date() include today's year, while anniv includes real birth date? if so:

Format( anniv, "mmdd" ) BETWEEN Format( now, "mmdd" ) AND Format( now +
30, "mmdd")

or

DateSerial( Year(now), Month(anniv), Day(anniv) ) BETWEEN date( ) AND
date( )+30



Vanderghast, Access MVP

 

[Hurrikane4]

That did work, thank you very much!

date() include today's year, while anniv includes real birth date? if so:

Format( anniv, "mmdd" ) BETWEEN Format( now, "mmdd" ) AND Format( now +
30, "mmdd")

or

DateSerial( Year(now), Month(anniv), Day(anniv) ) BETWEEN date( ) AND
date( )+30



Vanderghast, Access MVP

I'm trying to get the query to check if an anniversary date is within the
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")

 

[Hurrikane4]

That also worked, thank you for your help.

I'm trying to get the query to check if an anniversary date is within the
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")

Using your Greater Than and Less Than approach:
Expr1:IIf(([Anniv] <=Date() + 30) And ([Anniv]>= Date()),"Yes","No")

Perhaps this approach might be simpler:
Expr1:IIf([Anniv] Between Date() and Date() + 30,"Yes","No")


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

 

[Hurrikane4]

Thank you for your help.

Don't add numbers to date fields. It can be problematic. Use DateAdd()
instead. It allows you to add days, months, whatever you want.

In your case:
IIf([Anniv]>Date() AND [Anniv]<DateAdd("d",30,date()), "Yes", "No")

OR, IIf([Anniv]>Date() AND [Anniv]<Dateadd("m",1,date()), "Yes",
"No")which would check for dates within a month, whether 30 days, 31 or 28.

Phil

I'm trying to get the query to check if an anniversary date is within the
next 30 days, so I tried using this formula, but it did not work.
Your help is greatly appreciated.
Expr1: IIf([Anniv]>Date() AND [Anniv]<Date()+30, "Yes", "No")

.