How can I make query parameters optional? 2

[Gaz1634]

I have a query that selects particular data between two dates based on two parameters, is it possible to set the query so that if the parameters are left blank all the data will be returned, at the moment it just returns nothing if it is blank.

Hope this makes sense.

 

[smedvid]

I don't think u can do it within the Query (SQL). My approach was to create a function that returned the SQL String based on if and how the (2) date fields were filled. I build an SQL String then Execute it.

(4) Possible Conditions.
...
Dim lcSQL as String
Dim lcWhere as String
Dim ThisDB as DAO.Database

lcSQL = "SELECT f1,f2 FROM table WHERE "
Set ThisDB = CurrentDB

...
'Optional Dates - Build SQL Statement
If IsNull(Me.txtStartdate.VALUE) And IsNull(Me.txtEnddate.VALUE) Then
' No Code Changes Needed!
ElseIf IsNull(Me.txtStartdate.VALUE) = False And IsNull(Me.txtEnddate.VALUE) Then
lcWhere = lcWhere & " AND " & "(CourseStartDate >= #" & Me.txtStartdate.VALUE & "#)"
ElseIf IsNull(Me.txtStartdate.VALUE) And IsNull(Me.txtEnddate.VALUE) = False Then
lcWhere = lcWhere & &quot; AND &quot; & &quot;(CourseStartDate <= #&quot; & Me.txtEnddate.VALUE & &quot;#)&quot;
ElseIf IsNull(Me.txtStartdate.VALUE) = False And IsNull(Me.txtEnddate.VALUE) = False Then
lcWhere = lcWhere & &quot; AND &quot; & &quot;(CourseStartDate >= #&quot; & Me.txtStartdate.VALUE & &quot;# AND &quot; & &quot;CourseStartDate <= #&quot; & Me.txtEnddate.VALUE & &quot;#)&quot;
End If

lcSQL = lcSQL & lcWhere
...
ThisDB.EXECUTE lcSQL, DbOnError...


Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Tranman]

Gaz,
Not sure if this is the answer you are looking for, but here's a shot:

Key the following as criteria in your query:

Between IIf(IsNull([LowDate]),#01/01/1900#,[LowDate]) And IIf(IsNull([HighDate]),#12/31/4444#,[HighDate])

It works like this:
Each time the query is run, it asks you for LowDate. You can either enter a date, or just press the enter key. If you enter a date, that is the lower date for the criteria. If you just press enter (null), the lower date is set to some very low date of your choice.

After processing the LowDate, you are prompted for the HighDate. Once again, you may either enter a valid date, or just press enter. If you enter a valid date, that is the upper date for your criteria. If you just press enter, the upper date is set to some very high value of your choice.

The net effect of all of this is that if you just enter a LowDate and no HighDate, you get all rows where the date is >= LowDate. If you just enter a HighDate, and no LowDate, you get all rows with a date <= HighDate. If you just press enter twice, you get all of the rows in the table. Of course, if you enter both a low and high date, you get all of the rows with dates between LowDate and HighDate (inclusive).

Hope that helps.

Tranman

 

[MichaelRed]

use the &quot;like&quot; predicate with seperate srtart and end date fields. Include conditional logic to subst &quot;*&quot; for missing date(s)




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PaulBricker]

You can use this expression on your criteria line. If the parameters are filled in you get the start and end date. If not you get all the records.

Between [Enter Start Date] and [Enter End Date] Or [Enter Start Date] Is Null And [Enter End Date] Is Null


Paul

 

[Gaz1634]

Thanks for everyones help

This information has helped a lot.

 

[NoviceGuy]

Hello,
Sorry to sneak in here. I'm doing the exact same thing and the suggestion above worked great in all cases except where the value for the date field in the database can be blank (is not required) in which case the stmt:

Between IIf(IsNull([LowDate]),#01/01/1900#,[LowDate]) And IIf(IsNull([HighDate]),#12/31/4444#,[HighDate])

This doesn't pick up the records where date is blank, since blank is not between #01/01/1900# and #12/31/4444#. I'd like to grab them too. Any way to adjust the code to include any value in the date field (even blank/null) if the user doesn't specify this criteria?

I couldn't get the subsequent suggestion from Paul to work because I am lame.

Thanks

 

[Tranman]

NoviceGuy,
This should do the trick.

Between IIf(IsNull([LowDate]),#01/01/1900#,[LowDate]) And IIf(IsNull([HighDate]),#12/31/4444#,[HighDate]) or Is Null

Tranman

 

[NoviceGuy]

Tranman,
I apologize for being unclear, my bad. In the case you have suggested, I believe I'll get the records where the date is null all of the time. I actually only want to pick up these records in cases where the user has not specified a date range criteria (in which case I use the code to substitute in the bogus range that is very broad). Moral of the story, I only want to use the date range as a criteria at all if the user puts something in, otherwise, give me everything, whether a value or null.
Thanks,
Brett

 

[PaulBricker]

You should be able to copy and paste my expression directly to the Criteria line for your date field. When you run the query you will be prompted once for [Enter Start Date] and once for [Enter End Date]. If you leave them blank you will get all the records. If you fill them in you will get records between your parameter dates but that probably won't solve your problem with Null date values. If Tranman's works for that I would go with his.

Paul

 

[NoviceGuy]

Hello,
Paul, when I type in your code into the criteria line, access interprets it into to separate criteria, the between clause stays with the date field, and the or is null clause moves to the right in the design grid under two new fields: [Enter Start Date] and [Enter End Date], seems wierd. It automatically puts the is null in the OR row. This doesn't work.
Any help would be fantastic. I've tried all of the following, none seem to grab the records where date (called target date) is null only when the user doesn't specify a date range. Some attempts leave them off all of the time, and others grab them all of the time.


Attempt 1:
Between nz([Forms]![Parameter1]![TargetDateFrom],#1/1/1900#) And nz([Forms]![Parameter1]![TargetDateTo],#12/31/4444#)

Attempt 2:
Between IIf(IsNull([Forms]![Parameter1]![TargetDateFrom]),
#1/1/1900#,[Forms]![Parameter1]![TargetDateFrom]) And IIf(IsNull([Forms]![Parameter1]![TargetDateTo]),#1
2/31/4444#,[Forms]![Parameter1]![TargetDateTo])

Attempt 3: (same but added or is null at end.
Between IIf(IsNull([Forms]![Parameter1]![TargetDateFrom]),
#1/1/1900#,[Forms]![Parameter1]![TargetDateFrom]) And IIf(IsNull([Forms]![Parameter1]![TargetDateTo]),#1
2/31/4444#,[Forms]![Parameter1]![TargetDateTo]) or is null

Thanks much!

Noviceguy

 

[Tranman]

NoviceGuy,
I have an idea for a function that will accomplish what you want, but unfortunately, the boss thinks I should do some work today. If you continue to be frustrated, I will try to play with this after work.

Paul,
Thanks for participating in this.

Tranman (also a Paul)

 

[PaulBricker]

Access splits the phrase automatically so that is normal. I didn't bother to tell you because is isn't easy to explain.
This is my where clause when using the parameters with a form. This may help.

WHERE ((([Work Orders].[Date Issued]) Between [Forms]![Form1]![Textbox1] And [Forms]![Form1]![Textbox2])) OR ((([Forms]![Form1]![Textbox1]) Is Null) AND (([Forms]![Form1]![Textbox2]) Is Null))


Paul

 

[Tranman]

NoviceGuy,
Here's one that works. As the other Paul said, you have to split the where clause. This one does it by calculating a string based upon the date field, then calculates another string based upon whether LowDate and HighDate are both null. This expression is &quot;or'ed&quot; with the between clause to include lines where the date is null AND both LowDate and HighDate are null.

SELECT CUST.CUST_NBR, CUST.F_NAME, CUST.MI, CUST.L_NAME, CUST.DOB
FROM CUST
WHERE (((CUST.DOB) Between IIf(IsNull([LowDate]),#1/1/1900#,[LowDate]) And IIf(IsNull([HighDate]),#12/31/4444#,[HighDate]))) OR (((IIf(IsNull([DOB]),&quot;xxx&quot)=IIf(IsNull([LowDate]),IIf(IsNull([HighDate]),&quot;xxx&quot)));


I know it's ugly, but it works the way you wanted it to. This has been fun.

Tranman