Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Question Unanswered: Advanced query with nested iffs

    Hey everyone,

    Background:
    So I an equipment database. Part of that database tracks maintenance on equipment.

    What I have:
    Okay, bear with me here... I have a form, frmMainMenu. frmMainMenu has a navigation control for various sections of my database, one of which is maintenance. This navigation section has another navigation control within it with each of the headers as a due date (Today, this week, etc.). Each of those tabs has a maintenance report as its target, which has a query of all of the maintenance tasks on the tasks table as its source.

    Where I'm stuck:
    A field in that query takes the difference between the current data and the due date of a task

    Code:
    Days Left: DateDiff("d",Now(),[DueDate])
    I am trying to get this query to filter based on the tab that I have selected on the form. For example, if "Due Today" is selected, I want the criteria to be <=1.

    I tried using an iff statement with no luck. To test for functionality I tried a due <=1 else >1.

    Here is the equation:

    Code:
    Iff([Forms]![frmMainMenu]![NavigationSubform].[Form]![nbtnToday].[Enabled],(DateDiff("d",Now(),[DueDate]))<=1,(DateDiff("d",Now(),[DueDate]))>1)
    I'm not sure why it isn't working... Appreciate any input.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need a where clause that limits the rows returned to match your requirements

    lets say your control (mycontrol) is in a form called (myform)

    your where clause becomes
    WHERE datediff("d",date(), duedate)>forms!myform!mycontrol.value

    replace myform with the actual name of the form
    mycontrol with the actual name of the control

    as no no doubt you are usign gthe query designer (the annoying piece of software that comes between the developer and the real SQL) then you will need to put
    datediff("d",date(), duedate)>forms!myform!mycontrol.value in the criteria as to where it goes I don't know as I don't use the query designer unless Im really really forced to
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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