Date Formula using Now()

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Date Formula using Now()

Post by Leesha »

Hi,

I'm using the following forumula in a query but its not working. The result is coming up as "Closed" even though the date in [partyEndDate] is 2/27/2012. I'm not sure what I'm missing.

Thanks,
Leesha

Status: IIf([partyEndDate]<Now(),"Closed",IIf([partyenddate]=Now(),"Open",IIf([partyenddate]>Now(),"Closed")))

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date Formula using Now()

Post by HansV »

Your expression will return "Closed" if PartyEndDate is either before or after the exact moment of "Now". Since it's extremely unlikely that PartyEndDate will ever exactly coincide with "Now", the expression will ALWAYS return "Closed".

Perhaps you meant something different?
Best wishes,
Hans

Leesha
BronzeLounger
Posts: 1484
Joined: 05 Feb 2010, 22:25

Re: Date Formula using Now()

Post by Leesha »

:groan: I can't believe I missed that!!! Long day.
Thanks!

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date Formula using Now()

Post by HansV »

If you want to check for the current date instead of the current exact moment, use Date() instead of Now().
Best wishes,
Hans

User avatar
SteveH2508
NewLounger
Posts: 18
Joined: 24 Feb 2010, 20:38
Location: Chelsea, Greater London, England

Re: Date Formula using Now()

Post by SteveH2508 »

You can do this more efficiently as well

Status: IIf([partyEndDate]=Date(),"Open","Closed")
Access/Excel Dabbler
Windows XP SP3/Windows 7/O2K/O2007