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")))
Date Formula using Now()
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date Formula using Now()
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?
Perhaps you meant something different?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Date Formula using Now()
I can't believe I missed that!!! Long day.
Thanks!
Thanks!
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Date Formula using Now()
If you want to check for the current date instead of the current exact moment, use Date() instead of Now().
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 18
- Joined: 24 Feb 2010, 20:38
- Location: Chelsea, Greater London, England
Re: Date Formula using Now()
You can do this more efficiently as well
Status: IIf([partyEndDate]=Date(),"Open","Closed")
Status: IIf([partyEndDate]=Date(),"Open","Closed")
Access/Excel Dabbler
Windows XP SP3/Windows 7/O2K/O2007
Windows XP SP3/Windows 7/O2K/O2007