Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Query formula (XP)

    Hi!

    I have a query with [Date Due Back] and [Date Returned]. I need to calculate whether the record was returned timely but am not sure how to do it. I'm assuming I need an if than formula or something of that nature. Something like ..............Returned Timely: if [Date Returned] > than [Date Due Back] than [Returned Timely] = false, else [Returned Timely] = true.

    I can do this in Excel but can't seem to write it in an Access Query.

    I'm open to other suggestions as to how best acheive this.

    Thanks,
    Leesha

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query formula (XP)

    In a query, you can use a calculated field:

    Returned Timely: [Date Returned]<=[Date Due Back]

    The less than or equal to comparison will result in True or False, there is no need to use an IIf function here.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query formula (XP)

    As always, WOW!! That worked beautifully. Thanks also for the explanation behind why it worked!

    Leesha

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Query formula (XP)

    Just for your information, the syntax for doing this with an if in a query is this

    Returned Timely: IIf( [Date Returned] > [Date Due Back] ,False, True)

    * It is IIF standing for Immedate IF
    * The syntax is IIF(Test , Result if true , Result if false)
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query formula (XP)

    Thank you for the explanation! Now I can "see" it in my head better as to why it works.

    Leesha

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •