Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: IIF Query/Date Format

    I am setting up a query that pulls a range of dates from a form, or if the user checks a radio that says "All Dates", it shows all records. It looks like this:

    IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])

    This has not worked on neither the true nor false side.

    A straight Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] works fine.

    Specifying a date e.g. #9/15/2003# has worked for the true side, but the false remains fruitless.

    Please help....!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    maybe you wont like the idea, but howabout a couple of hidden text boxes on the form hDate1 and hDate2

    _AfterUpdate on your radio sets
    hDate1 = Date1 or 1
    hDate2 = Date2 or 9999999
    (WARNING this is another Y2K-bug thing. it fails after 27 thousand years)

    and then
    Between [Forms]![Reports]![hDate1] And [Forms]![Reports]![hDate2]

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Oct 2003
    Location
    São Paulo - Brazil
    Posts
    91

    Re: IIF Query/Date Format

    Originally posted by efpav
    I am setting up a query that pulls a range of dates from a form, or if the user checks a radio that says "All Dates", it shows all records. It looks like this:

    IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])

    This has not worked on neither the true nor false side.

    A straight Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] works fine.

    Specifying a date e.g. #9/15/2003# has worked for the true side, but the false remains fruitless.

    Please help....!
    Hi!

    Avoid using iif() statement.

    Look at this article, about Application Optimization:

    http://msdn.microsoft.com/library/de...asp?frmae=true

    Instead of using iif() create two queries:

    Code:
    If Condition = true then
      query 1
    else
       query 2
    endif
    And it´s easier to debug !
    My environment: Windows XP/ Access 2000 - Using Microsoft DAO 3.6 Library

  4. #4
    Join Date
    Nov 2003
    Posts
    14
    Thanks. I have at least a couple of new directions to go in now. I will post again to let you know how it worked.

    I read why not to use an IIF in a query, but it is an efficiency thing, not a problem with the function. So then why did the expression not work?

    Thanks again!

  5. #5
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85

    Re: IIF Query/Date Format

    Originally posted by efpav
    I am setting up a query that pulls a range of dates from a form, or if the user checks a radio that says "All Dates", it shows all records. It looks like this:

    IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])

    This has not worked on neither the true nor false side.

    A straight Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] works fine.

    Specifying a date e.g. #9/15/2003# has worked for the true side, but the false remains fruitless.

    Please help....!
    put like in front of the IIF statement
    Last edited by lodewijk; 11-26-03 at 11:05.

  6. #6
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    I don't understand the point of the query. The 'true' part ('*') means 'all fields'. But the 'false' part looks like a 'WHERE' clause. It doesn't make sense to me, but maybe I'm missing something. What is the rest of the SQL?

    IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])

    This has not worked on neither the true nor false side.

    A straight Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] works fine.

    Specifying a date e.g. #9/15/2003# has worked for the true side, but the false remains fruitless.

  7. #7
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by pd9n
    I don't understand the point of the query. The 'true' part ('*') means 'all fields'. But the 'false' part looks like a 'WHERE' clause. It doesn't make sense to me, but maybe I'm missing something. What is the rest of the SQL?

    IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])

    This has not worked on neither the true nor false side.

    A straight Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] works fine.

    Specifying a date e.g. #9/15/2003# has worked for the true side, but the false remains fruitless.
    The statement you put as crteria is going to be

    like IIF(([Forms]![Reports]![AllDates])=-1, "*", Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])


    The result if alldates =-1 will be like "*"

  8. #8
    Join Date
    Nov 2003
    Posts
    14
    That's exactly what I want, but it doesn't work. I want to be able to specify a date range, or check off "all dates" and have every date come up.

  9. #9
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by efpav
    That's exactly what I want, but it doesn't work. I want to be able to specify a date range, or check off "all dates" and have every date come up.
    Sorry for the wrong statement which only applies for a field which contains no date.

    You need
    Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _
    Forms!FormName!StartDate Is Null

    Your statement will be

    Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2])
    or [Forms]![Reports]![Date1] is null

    Otherwise see this url http://support.microsoft.com/default...NoWebContent=1

  10. #10
    Join Date
    Nov 2003
    Posts
    14
    That was the answer I was looking for !!!

    Thank you so much !

  11. #11
    Join Date
    Mar 2003
    Location
    Bogota
    Posts
    67
    I don't think you can set up that kind of program-flow logic in SQL, at least not to where you're building SQL (i.e. choosing from 'like' or 'between') within the SQL statement itself.

    You can, however, do that in VBA.

    If you want to avoid VBA, then you can set up two queries, one pulling all dates and one using the <Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2]> clause.

    Then, use a macro, where you can specify conditions, and depending on those conditions call either query.

    Of course, you could also put that into code, so that you would have, in your 'on click' code, something like...

    If AllDates = -1 Then
    docmd.openquery "<the all dates one>"
    else
    docmd.openquery "<the date range one>"
    end if

  12. #12
    Join Date
    Nov 2003
    Posts
    14
    This works like I wanted it to:

    Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] Or [Forms]![Reports]![Date1] Is Null

    It pulls up all records between Date1 and Date2, but if Date1 is Null, it pulls up all records.

    Awesome!

    Now what I need is for Radio1 to toggle in and out of a Null value for Date1.

    Any ideas ?


    Thank you so very much!

  13. #13
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by efpav
    This works like I wanted it to:

    Between [Forms]![Reports]![Date1] And [Forms]![Reports]![Date2] Or [Forms]![Reports]![Date1] Is Null

    It pulls up all records between Date1 and Date2, but if Date1 is Null, it pulls up all records.

    Awesome!

    Now what I need is for Radio1 to toggle in and out of a Null value for Date1.

    Any ideas ?


    Thank you so very much!
    What do you mean by toggle in and out? Something like switching between a date for date1 and date1 is null?

  14. #14
    Join Date
    Nov 2003
    Posts
    14
    Exactly

  15. #15
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    85
    Originally posted by efpav
    Exactly
    I always use a separate form for selecting data. Then I mention theze fields in the criteria used in the Query.
    The query is called on by a form or report. When I close (action on closing) the report or form the date1 on the separate form is set to Null.
    Followed by a refesh on this form.
    This means that date1 is always Null unless you give a date.
    Other possibility is a messagebox which occures when date1 is getting the focus. Then you enter a date or nothing and click on OK button. Date1 gets the value mentioned in de messagebox.

    good luck

Posting Permissions

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