Parameter query with Start/End Date problem

[Kathy]

Hello, I am building a parameter query using query by form. The form has 3
criteria: Physician Name, Start Date, End Date. I have used IS NULL in the OR
criteria for the Physician Name, so if that field is left blank, then all the
physicians will show. So far if I put in the physican name with start and end
dates, I receive back the correct information. However if I leave the
physican field blank and just put in start and end dates, I get all records
back again. Also if I put in the physican name and leave the start and end
dates blank, I also get all the records returned. This is my problem as I
would like to be able to leave the physican name blank, so I will see all
physicians, but filtered for start and end date. Or if I just put in the
physican name and blank dates, I will see all the dates for that physician. I
have tried moving the criteria to different rows of the OR criteria, but
still did not fix the entire problem. Any help is greatly appreciated.

 

[Mike Busch]

Place (& "*") after your ending bracket. (Without the parentices)This will
show all records for that field.

 

[Amy Blankenship]

Hello, I am building a parameter query using query by form. The form has 3
criteria: Physician Name, Start Date, End Date. I have used IS NULL in the
OR
criteria for the Physician Name, so if that field is left blank, then all
the
physicians will show. So far if I put in the physican name with start and
end
dates, I receive back the correct information. However if I leave the
physican field blank and just put in start and end dates, I get all
records
back again. Also if I put in the physican name and leave the start and end
dates blank, I also get all the records returned. This is my problem as I
would like to be able to leave the physican name blank, so I will see all
physicians, but filtered for start and end date. Or if I just put in the
physican name and blank dates, I will see all the dates for that
physician. I
have tried moving the criteria to different rows of the OR criteria, but
still did not fix the entire problem. Any help is greatly appreciated.

You may find this useful
http://support.microsoft.com/kb/290178/

 

[Kathy]

Hi Mike and thank you for your response. I am pretty green at this stuff, but
I do feel it is something simple that I am missing...as I just may be placing
your suggestion in the wrong area. Here is what I have so far:
In the query, under criteria for Physician Name is:

[Forms]![frmParameter]![cboPhysician] and in the OR part IS NULL

Then in the Date is the criteria:

Between Nz([Forms]![frmParameter]![txtStartDate],0) AND
Nz([Forms]![frmParameter]![txtEndDate],Date())

When I use the Parameter form to run the query, it is returning the correct
information, except if I leave the Physican Name blank and fill in the Start
and End Dates. It returns all the records, in other words, it does not filter
by the dates entered. I tried putting in your suggestion, but it did not
correct the problem and I am wondering if the Between..And code needs more
help or I put your suggestion in the wrong place. Sorry for the wordiness and
your help is much appreciated.

 

[John Spencer]

Kathy,
IF your Physician field and your date field ALWAYS have a value and ar never
null, you can use the following

Field: Physician
Criteria: LIKE Nz([Forms]![frmParameter]![cboPhysician],"*")

Field: VisitDate
Criteria: Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) AND
Nz([Forms]![frmParameter]![txtEndDate],Date())

If the Physician field or the date field can be blank/null then post back
for a more complicated solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Mike and thank you for your response. I am pretty green at this stuff,
but
I do feel it is something simple that I am missing...as I just may be
placing
your suggestion in the wrong area. Here is what I have so far:
In the query, under criteria for Physician Name is:

[Forms]![frmParameter]![cboPhysician] and in the OR part IS NULL

Then in the Date is the criteria:

Between Nz([Forms]![frmParameter]![txtStartDate],0) AND
Nz([Forms]![frmParameter]![txtEndDate],Date())

When I use the Parameter form to run the query, it is returning the
correct
information, except if I leave the Physican Name blank and fill in the
Start
and End Dates. It returns all the records, in other words, it does not
filter
by the dates entered. I tried putting in your suggestion, but it did not
correct the problem and I am wondering if the Between..And code needs more
help or I put your suggestion in the wrong place. Sorry for the wordiness
and
your help is much appreciated.
--
Kbelo

Place (& "*") after your ending bracket. (Without the parentices)This
will
show all records for that field.

 

[Kathy]

Hello again and thank you for helping....I would like that the form (I am
using query by form) be able to be blank/null for any or all values. I have
one combo box (Physician Name) and two textboxes (Start Date, End Date) on
the form. If ALL are left blank, then I would like to have returned all
physicians and all dates. If Physican is left blank, but Start/End is given,
then returns all physicians for that date range. If physician is given, and
Start/End is blank, then all dates are returned for that physician.
Any suggestions are greatly appreciated. Thank you.
--
Kbelo

Kathy,
IF your Physician field and your date field ALWAYS have a value and ar never
null, you can use the following

Field: Physician
Criteria: LIKE Nz([Forms]![frmParameter]![cboPhysician],"*")

Field: VisitDate
Criteria: Between Nz([Forms]![frmParameter]![txtStartDate],#1/1/1900#) AND
Nz([Forms]![frmParameter]![txtEndDate],Date())

If the Physician field or the date field can be blank/null then post back
for a more complicated solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi Mike and thank you for your response. I am pretty green at this stuff,
but
I do feel it is something simple that I am missing...as I just may be
placing
your suggestion in the wrong area. Here is what I have so far:
In the query, under criteria for Physician Name is:

[Forms]![frmParameter]![cboPhysician] and in the OR part IS NULL

Then in the Date is the criteria:

Between Nz([Forms]![frmParameter]![txtStartDate],0) AND
Nz([Forms]![frmParameter]![txtEndDate],Date())

When I use the Parameter form to run the query, it is returning the
correct
information, except if I leave the Physican Name blank and fill in the
Start
and End Dates. It returns all the records, in other words, it does not
filter
by the dates entered. I tried putting in your suggestion, but it did not
correct the problem and I am wondering if the Between..And code needs more
help or I put your suggestion in the wrong place. Sorry for the wordiness
and
your help is much appreciated.
--
Kbelo

Place (& "*") after your ending bracket. (Without the parentices)This
will
show all records for that field.

:

Hello, I am building a parameter query using query by form. The form
has 3
criteria: Physician Name, Start Date, End Date. I have used IS NULL in
the OR
criteria for the Physician Name, so if that field is left blank, then
all the
physicians will show. So far if I put in the physican name with start
and end
dates, I receive back the correct information. However if I leave the
physican field blank and just put in start and end dates, I get all
records
back again. Also if I put in the physican name and leave the start and
end
dates blank, I also get all the records returned. This is my problem as
I
would like to be able to leave the physican name blank, so I will see
all
physicians, but filtered for start and end date. Or if I just put in
the
physican name and blank dates, I will see all the dates for that
physician. I
have tried moving the criteria to different rows of the OR criteria,
but
still did not fix the entire problem. Any help is greatly appreciated.

 

[John Spencer]

A form has controls.

A table has fields.



If you are leaving the controls on the parameter blank and the fields in
the table always have a value then what I suggested in my earlier post
should work when you leave the controls on the parameter form blank (null)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Kathy]

Thank you...your suggestion has fixed the problem.