Query Parameters: Between Or ALL (2002)

Topic

New Reply

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!

Reply | Quote

Replies

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Hans, for some reason when I enter the parameters you stated, even when I press 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!!

Reply | Quote

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 for instructions.

Reply | Quote

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote