Query criteria based on dates added on form

Posted on 2011-05-11
Last Modified: 2012-05-11

I have an access query based on dates added on form

When the start date filter is null, I want to select all dates.

The "*"  doesn't work
like (iif(([Forms]![frm_Prime]![filtStartDate])="";"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))

Question by:Karl001
    LVL 77

    Expert Comment

    Where ([dateSchedul]>=[Forms]![frm_Primes]![filtStartDate] and [dateSchedul]<=[Forms]![frm_Prime]![filtEndDate]) or [Forms]![frm_Primes]![filtStartDate] is null

    LVL 119

    Expert Comment

    by:Rey Obrero

    try this

    where iif([Forms]![frm_Prime]![filtStartDate]= "",[dateSchedul], ([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate])


    where iif([Forms]![frm_Prime]![filtStartDate] & "" = "",[dateSchedul], ([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate])
    LVL 42

    Accepted Solution

    I rather like this, which accepts either date as null.  If startdate is null it pulls everything before end date; if enddate is null, it pulls everything after startdate.  If both dates are null, it pulls everything

    [dateSchedul]>=nz([Forms]![frm_Prime]![filtStartDate],[dateSchedul]) and
    LVL 51

    Expert Comment

    or try this:

    (nz([Forms]![frm_Prime]![filtStartDate])="" or [dateSchedul]>[Forms]![frm_Prime]![filtStartDate])
    (nz([Forms]![frm_Prime]![filtEndDate])="" or [dateSchedul]<=[dateSchedul])

    or just this

    ([Forms]![frm_Prime]![filtStartDate]="" or [dateSchedul]>[Forms]![frm_Prime]![filtStartDate])
    (Forms]![frm_Prime]![filtEndDate]="" or [dateSchedul]<=[dateSchedul])

    LVL 19

    Expert Comment

    by:Eric Sherman
    Also, I recommend adding a few lines of VBA code before the Query runs that will validate  the start and end date controls on your form.  

    If IsNull(Me.filtStartDate) Or IsNull(Me.filtEndDate) Then
        MsgBox "Start Date and End Date are both required fields."
        Exit Sub
    End If

    DoCmd.OpenQuery ......your query here

    LVL 44

    Expert Comment


    like (iif(([Forms]![frm_Prime]![filtStartDate])="";"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))

    should be this:

    like (iif(([Forms]![frm_Prime]![filtStartDate])=Null;"*";([dateSchedul])>=[Forms]![frm_Primes]![filtStartDate] and ([dateSchedul])<=[Forms]![frm_Prime]![filtEndDate]))

    Author Closing Comment

    I tested the 3 first solution.
    sol 1 is for "null" date
    sol 2, I tried and I was not able to have result.
    sol 3, works wells

    Thanks everyone

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now