Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Parameters: Between Or ALL (2002)

    I have a query I use for a report and I'd like the option of having only those records falling between the dates I enter as parameters or ALL of the records. No problem doing either but I can't figure out how to combine them as an option. I'm sure I'm missing something obvious. I have other reports where I have choices that are either one particular criteria or everything but can't figure out using Between.

    Thanks!

  2. #2
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Hi Peggy:

    Assuming you're generating this report from a user form using VBA on a click event of a button, you could code it so that if the user selects start and end dates from combo boxes on the form, your SQL grabs those dates into variables, (i.e., strSQL = "SELECT myTable.Whatever WHERE mytable.myDatefield BETWEEN #" & dtStart & "# AND #" & dtEnd & "#;" and if they don't (i.e., Nz(me.cmbEnd,0)=0 and Nz(me.cmbStart,0)=0) then your SQL does not include any WHERE criteria on the date fields. Save the SQL to your querydef object (qdef.sql = strSQL ) so you can keep the same qerydef for the report and don't have to filter it (a big pain, imho).

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Kathryn, you credit it me with far too much expertise. I am not generating the report from a form using VBA. I'm the only one using the database and I just run the report with the query popping up asking for my input. I'm afraid you lost me completely with your reply but I appreciate the effort. Thank you.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Sorry about that. You'll need whoever wrote the query for the report to fix it for you. If that person is no longer around and you're working with an .mde file, you can always just enter a very early (like last century) start date and a later than today end date when the query prompts you to return all the dates.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    I wrote the database, the query and the report. Most of the records have no date in that field right now so I believe that using dates spanning a 100 years isn't going to solve my problem. I have done other queries where I have a choice such as one state or all states. But that doesn't work for me when using Between. I can always re-do the query when I need to have all the records, just thought it would be easier to have another option added on.

    Thanks again.

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

    Re: Query Parameters: Between Or ALL (2002)

    You can use a condition like this in the Criteria line of the query used as record source:

    (>=[Start Date] Or [Start Date] Is Null) And (<=[End Date] Or [End Date] Is Null)

    If the user enters both a start date and an end date, all records are returned for which the relevant field is between the start date and end date.
    If the user enters only a start date and leaves the end date blank, all records are returned for which the relevant field is on or after the start date.
    If the user enters only an end date and leaves the start date blank, all records are returned for which the relevant field is on or before the end date.
    If the user leaves both start date and end date blank, all records are returned.

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Hans, for some reason when I enter the parameters you stated, even when I press <enter> when start and end dates are requested I still get only records that have dates in them.

    I've attached a screenshot of the way Access interpreted what I wrote in (which was exactly what you wrote). What have I done wrong?

    Thank you!!
    Attached Images Attached Images

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

    Re: Query Parameters: Between Or ALL (2002)

    That looks correct (Access always reorganizes the criteria that way). When I test it, it works as intended.

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Ah, figured our why your criteria didn't work for me: I have only a couple of records that have dates in them. And they are the only ones that show. Even when I have no criteria only the test records show. Obviously I need to have my date field not be null.

    Can you suggest what I should have as a default value for my date field (prefer it not be a date - just something equivalent to zero)?

    Thanks! Problem almost solved. You guys are great!

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

    Re: Query Parameters: Between Or ALL (2002)

    If you have a real date/time field you can just leave it blank as default. If you'd rather have a non-blank default value that doesn't look like a 'real' date you could use something like 9/9/9999.

  11. #11
    2 Star Lounger
    Join Date
    Jan 2004
    Location
    Pontiac, Michigan, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters: Between Or ALL (2002)

    Thank you for all your help, Hans (and Kathryn)! Problem all solved.

Posting Permissions

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