Parameter query with Start/End Date problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kathy - 28 Jun 2007 16:46 GMT

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.

Signature

Kbelo

Reply to this Message

Mike Busch - 28 Jun 2007 17:12 GMT

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
[quoted text clipped - 9 lines]
> 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.

Reply to this Message

Kathy - 29 Jun 2007 00:26 GMT

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.

Signature

Kbelo

> Place (& "*") after your ending bracket. (Without the parentices)This will
> show all records for that field.
[quoted text clipped - 12 lines]
> > 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.

Reply to this Message

John Spencer - 29 Jun 2007 12:57 GMT

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.

Signature

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
[quoted text clipped - 50 lines]
>> > but
>> > still did not fix the entire problem. Any help is greatly appreciated.

Reply to this Message

Kathy - 29 Jun 2007 23:34 GMT

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.

Signature

Kbelo

> Kathy,
> IF your Physician field and your date field ALWAYS have a value and ar never
[quoted text clipped - 64 lines]
> >> > but
> >> > still did not fix the entire problem. Any help is greatly appreciated.

Reply to this Message

John Spencer - 30 Jun 2007 01:51 GMT

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
'====================================================

> 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
[quoted text clipped - 4 lines]
> Start/End is blank, then all dates are returned for that physician.
> Any suggestions are greatly appreciated. Thank you.

Reply to this Message

Kathy - 30 Jun 2007 17:48 GMT

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

Signature

Kbelo

> A form has controls.
>
[quoted text clipped - 19 lines]
> > Start/End is blank, then all dates are returned for that physician.
> > Any suggestions are greatly appreciated. Thank you.

Reply to this Message

Amy Blankenship - 28 Jun 2007 18:01 GMT

> 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
[quoted text clipped - 14 lines]
> 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/

Reply to this Message