Parameter Query

[PC User]

I have a query for a recordsource for a subform that has criteria for a
date range. Sometimes I don't need to use the date range. Is there
some way to make this query flexible to work when no date range is
entered and I can view all the data from the recordsource table? The
field name "Selected" is a checkbox. The query SQL is as follows:
====================================
SELECT tsubProgramList.ProgramID, tsubProgramList.Selected,
tsubProgramList.DueDate
FROM tsubProgramList
WHERE (((tsubProgramList.Selected)=Yes) AND ((tsubProgramList.DueDate)
Between [Forms]![frmMainEntry]![txtBeginningDate] And
[Forms]![frmMainEntry]![txtEndingDate]));
====================================
Thanks,
PC

 

[Rick Brandt]

I have a query for a recordsource for a subform that has criteria for a
date range. Sometimes I don't need to use the date range. Is there
some way to make this query flexible to work when no date range is
entered and I can view all the data from the recordsource table? The
field name "Selected" is a checkbox. The query SQL is as follows:
====================================
SELECT tsubProgramList.ProgramID, tsubProgramList.Selected,
tsubProgramList.DueDate
FROM tsubProgramList
WHERE (((tsubProgramList.Selected)=Yes) AND ((tsubProgramList.DueDate)
Between [Forms]![frmMainEntry]![txtBeginningDate] And
[Forms]![frmMainEntry]![txtEndingDate]));
====================================

Add criteria...

Or ([Forms]![frmMainEntry]![txtBeginningDate] Is Null
And [Forms]![frmMainEntry]![txtEndingDate] Is Null)

If both TextBoxes are left empty you should get all rows.

 

[PC User]

Thanks for your reply Rick,

I've added your suggestion. The query now looks like this:
==================================
SELECT tsubProgramList.ProgramID, tsubProgramList.Selected,
tsubProgramList.DueDate
FROM tsubProgramList
WHERE (((tsubProgramList.Selected)=Yes) AND ((tsubProgramList.DueDate)
Between [Forms]![frmMainEntry]![txtBeginningDate] And
[Forms]![frmMainEntry]![txtEndingDate]))OR
([Forms]![frmMainEntry]![txtBeginningDate] Is Null
And [Forms]![frmMainEntry]![txtEndingDate] Is Null);
==================================
Is this what you had in mind?
Thanks,
PC

 

[Rick Brandt]

Thanks for your reply Rick,

I've added your suggestion. The query now looks like this:
==================================
SELECT tsubProgramList.ProgramID, tsubProgramList.Selected,
tsubProgramList.DueDate
FROM tsubProgramList
WHERE (((tsubProgramList.Selected)=Yes) AND ((tsubProgramList.DueDate)
Between [Forms]![frmMainEntry]![txtBeginningDate] And
[Forms]![frmMainEntry]![txtEndingDate]))OR
([Forms]![frmMainEntry]![txtBeginningDate] Is Null
And [Forms]![frmMainEntry]![txtEndingDate] Is Null);
==================================
Is this what you had in mind?
Thanks,
PC

Yes, as long as the parenthetic grouping is correct I think that should work.
Did you try it?

 

[Douglas J. Steele]

While's Rick's suggestion is correct, you may find it more useful to try the
following:

SELECT tsubProgramList.ProgramID, tsubProgramList.Selected,
tsubProgramList.DueDate
FROM tsubProgramList
WHERE ((tsubProgramList.Selected) = True) AND (tsubProgramList.DueDate >=
[Forms]![frmMainEntry]![txtBeginningDate] OR
([Forms]![frmMainEntry]![txtBeginningDate] Is Null)
AND (tsubProgramList.DueDate <= [Forms]![frmMainEntry]![txtEndingDate] OR
[Forms]![frmMainEntry]![txtEndingDate] Is Null))

In this way, you could provide only txtBeginningDate and not txtEndingDate
and you'll get all records since the provided date, or only txtEndingDate
and not txtBeginningDate and you'll get all records before the provided date

 

[PC User]

Steve,
I will try your code in an update query also. I would think that
it would look like this wouldn't it?
===============================
UPDATE tsubProgramList SET tsubProgramList.Selected = Yes
WHERE
(((tsubProgramList.DueDate)>=[Forms]![frmMainEntry]![txtBeginningDate]))
OR (((tsubProgramList.DueDate)<=[Forms]![frmMainEntry]![txtEndingDate])
AND (([Forms]![frmMainEntry]![txtBeginningDate]) Is Null)) OR
((([Forms]![frmMainEntry]![txtBeginningDate]) Is Null) AND
(([Forms]![frmMainEntry]![txtEndingDate]) Is Null));
===============================
Thanks,
PC

 

[PC User]

Doug,
I will try your code in an update query also. I would think that

it would look like this wouldn't it?
==============================­=
UPDATE tsubProgramList SET tsubProgramList.Selected = Yes
WHERE
(((tsubProgramList.DueDate)>=[­Forms]![frmMainEntry]![txtBegi­nningDate]))

OR
(((tsubProgramList.DueDate)<=[­Forms]![frmMainEntry]![txtEndi­ngDate])

AND (([Forms]![frmMainEntry]![txtB­eginningDate]) Is Null)) OR
((([Forms]![frmMainEntry]![txt­BeginningDate]) Is Null) AND
(([Forms]![frmMainEntry]![txtE­ndingDate]) Is Null));
==============================­=
Thanks,
PC

 

[Douglas J. Steele]

It seems redundant to me to have the reference to
[Forms]![frmMainEntry]![txtB­eginningDate]) Is Null twice.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Doug,
I will try your code in an update query also. I would think that

it would look like this wouldn't it?
==============================­=
UPDATE tsubProgramList SET tsubProgramList.Selected = Yes
WHERE
(((tsubProgramList.DueDate)>=[­Forms]![frmMainEntry]![txtBegi­nningDate]))

OR
(((tsubProgramList.DueDate)<=[­Forms]![frmMainEntry]![txtEndi­ngDate])

AND (([Forms]![frmMainEntry]![txtB­eginningDate]) Is Null)) OR
((([Forms]![frmMainEntry]![txt­BeginningDate]) Is Null) AND
(([Forms]![frmMainEntry]![txtE­ndingDate]) Is Null));
==============================­=
Thanks,
PC