How to get parameter for date field to show all records 1

[MariaL]

Hello everyone. I created a parameter in a query which allows me to ask for a particular date range each time I run a query. I used the following: "Between [TYPE IN BEGINNING INS ELIG DATE] And [TYPE IN ENDING INS ELIG DATE]"
How can I leave this prompt, but pull all dates when I run the query? My goal is to be able to pull a certain range or to pull all dates each time I run the query. Thank you for your time and help

 

[SQLBI]

Maria,

Try it without the " marks;

WHERE inputDate BETWEEN [Enter Date 1] AND [Enter Date 2];

Hope this helps.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[MariaL]

I'm sorry, my stated code was misleading. I did not use the quotation in the query. any other ideas? Thank you for your response.

 

[SQLBI]

Maria,

I've just tried this on my own DB and it works fine. Are you entering the correct date format into the prompts, according to the field format of the table eg dd/mm/yyyy?



Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[MariaL]

Again, thank you for your response. When I type in a date range it works fine. But, if I enter through the field and leave the parameters blank, it will not pull any records. Is there anyway to leave the field blank and have it pull all records? Thank you!

 

[SQLBI]

Ah, sorry a little confusion on my part there.

I use Like "" (Empty box) a lot and it will return all records for things like last name etc, but i've never tried it on dates.

The only alternative i can think of would be something like Between 01/01/1900 And 27/08/2003, but that's not ideal.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[MariaL]

Yeah, you know how companies can be. I gave them two dates to use, but they say there must be a way to leave it blank without having to type in dates. Keep me in mind if you think of anything to help with this issue. Thank you!

 

[SQLBI]

The only other option i can think of is that you have a form with 2 text boxes for the date input.

Write 2 queries, 1 that takes the input from the form:

BETWEEN [Forms]![frmDateCheck]![txtStartDate] AND [Forms]![frmDateCheck]![txtEndDate]

and another that will return all records.

On the Click event of a command button, use an IF statement to decide which query to run.

IF Forms_frmDateCheck.txtStartDate <> &quot;&quot; AND Forms_frmDateCheck.txtEndDate <> &quot;&quot; THEN

DoCmd.OpenQuery &quot;qryDatesEntered&quot;

Else

DoCmd.OpenQuery &quot;qryDatesOmmitted&quot;

You get where i'm going...

That's how i'd probably tackle it.

Leigh Moore
LJM Analysis Ltd

http://www.ljm-analysis.co.uk

 

[CosmoKramer]

Maria,

Try something like this as the criteria in your query, of course changing to your query/table/field names where needed:

Between IIf([TYPE IN BEGINNING INS ELIG DATE] Is Null,DMin("[YourDateField]","YourTableorQuery"),[TYPE IN BEGINNING INS ELIG DATE]) And IIf([TYPE IN ENDING INS ELIG DATE] Is Null,DMax("[YourDateField]","YourTableorQuery"),[TYPE IN ENDING INS ELIG DATE])

If the beginning date parameter is left blank the earliest date in the table will be used and if the ending date is left blank, the latest date will be used. Conversely, if one or both parameters are entered, those will be used instead.

Let me know if this helps....

Hoc nomen meum verum non est.

 

[GoDawgs]

I found kind of a silly way:

Between IIf(IsNull([enter start date]),#01/01/1500#,[enter start date]) And IIf(IsNull([enter end date]),#01/01/3000#,[enter end date])

Basically still uses the between if they don't enter anything, but makes it between dates that they have to be between. Hope that helps.

Kevin

 

[MariaL]

THANK YOU THANK YOU THANK YOU! This worked great. I really appreciate your help! have a great day!

 

[CosmoKramer]

Just remember though, one downfall of using a multiple query method is remembering to update all query clones when modifications are necessary....

Hoc nomen meum verum non est.