none
Comparing Dates in Access using IIF function RRS feed

  • Question

  •  - When I use IIF function in MSAccess (2007-2010), it always returns the FALSE.  I have two dates, [Firm Date] and [Budget Date].  Regardless of the values in the two dates, or whether one or the other has no value, the returned value is always the FALSE value, in the case below, "2".  How do you compare dates?

    IIf([Firm Date]<[Budget Date],1,2)

    Would appreciate any help.

    Monday, January 7, 2019 2:49 PM

Answers

  • I figured out the problem.  If either of the dates were not initialized, it would always return the "false" answer.  I had to first test whether the date value was null using the IsNull function, then I could execute a comparison of dates only if both had values.
    • Marked as answer by PaulBakerGAI Monday, January 7, 2019 4:37 PM
    Monday, January 7, 2019 4:37 PM

All replies

  • Date fields in Access can store just date or date and time.  If you’re not sure which is stored in a date field and want to do a date comparison always take the integer part of the field.  The integer part of the field is the date, the decimal part is the time.

    IIf(Int([Firm Date])<Int([Budget Date]),1,2)


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

    Monday, January 7, 2019 3:24 PM
  • Hi Paul,

    What are the data types of your fields [Firm Date] and [Budget Date] in the table?

    Monday, January 7, 2019 4:30 PM
  • I figured out the problem.  If either of the dates were not initialized, it would always return the "false" answer.  I had to first test whether the date value was null using the IsNull function, then I could execute a comparison of dates only if both had values.
    • Marked as answer by PaulBakerGAI Monday, January 7, 2019 4:37 PM
    Monday, January 7, 2019 4:37 PM