Parameter Query to filter dates as entered in and if not to show N

[Agnelo Fernandes]

there is a date field in my parameter query. I have set the criteria as
[Enter Course Date], which is the input message that filters the records
accordingly based on the date entered in. This works fine.

What I want is, if the input message is left blank i.e no date is entered
for the prompt [Enter Course Date] then i want records with Null course dates
to be filtered? Currently, if the input message is left blank then no records
are filtered.


Can you please help me with this?

Thanks

 

[Allen Browne]

Switch the query to SQL View (View menu.)

Locate the WHERE clause, and change it so it looks like this (as one line):

WHERE IIf([Enter Course Date] Is Null, [SomeDate] Is Null,
[SomeDate] = [Enter Course Date])

Be sure to declare the parameter so Access knows it is a date/time type.
(Parameters on Query menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Agnelo Fernandes]

Hey that worked!! Thanks

Switch the query to SQL View (View menu.)

Locate the WHERE clause, and change it so it looks like this (as one line):

WHERE IIf([Enter Course Date] Is Null, [SomeDate] Is Null,
[SomeDate] = [Enter Course Date])

Be sure to declare the parameter so Access knows it is a date/time type.
(Parameters on Query menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

there is a date field in my parameter query. I have set the criteria as
[Enter Course Date], which is the input message that filters the records
accordingly based on the date entered in. This works fine.

What I want is, if the input message is left blank i.e no date is entered
for the prompt [Enter Course Date] then i want records with Null course
dates to be filtered? Currently, if the input message is left blank then
no
records are filtered.

 

[KARL DEWEY]

Somewhere I got the idea you could not use logical functions inside of an IIF
function as you did.
In the past I placed Like outside --
Like "*" & IIF([XX] = "Y", "Z", "A") & "*"
instead of
IIF([XX] = "Y", Like "*Z*", Like "*A*")

--
Build a little, test a little.

Switch the query to SQL View (View menu.)

Locate the WHERE clause, and change it so it looks like this (as one line):

WHERE IIf([Enter Course Date] Is Null, [SomeDate] Is Null,
[SomeDate] = [Enter Course Date])

Be sure to declare the parameter so Access knows it is a date/time type.
(Parameters on Query menu.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

there is a date field in my parameter query. I have set the criteria as
[Enter Course Date], which is the input message that filters the records
accordingly based on the date entered in. This works fine.

What I want is, if the input message is left blank i.e no date is entered
for the prompt [Enter Course Date] then i want records with Null course
dates to be filtered? Currently, if the input message is left blank then
no
records are filtered.

 

[John Spencer]

Check Allen's expression a little closer.

WHERE IIf([Enter Course Date] Is Null, [SomeDate] Is Null,
[SomeDate] = [Enter Course Date])

Notice that he is checking the field against a value or lack of value
depending on the initial argument.

Basically he is returning True (or False).
If the first argument is true, then if SomeDate is Null the expression
evaluates to true otherwise it evaluates to false. And similarly if the first
argument is true, then the third argument returns true for matches and not
true for no matches.

I think you were visualizing

WHERE SomeDate = IIF([Enter Course Date] Is Null, Is Null, [Enter Course Date])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County