Passign Multiple Criteria From a Form

G

Guest

Please help I am stuck.
I have a form which has three field to pass and what I want to do is if user
don't select any thing I want to shwo every thing but if they select patient
id only show that opatient record
and if they select a date criteria then show only that patient record with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1] And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
 
D

Duane Hookom

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or [Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything else)
then I would not place any of these criteria in the query.
 
G

Guest

Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2]) OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX Report]![txtenddate2]) OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

Duane Hookom said:
You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or [Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Tanweer said:
Please help I am stuck.
I have a form which has three field to pass and what I want to do is if
user
don't select any thing I want to shwo every thing but if they select
patient
id only show that opatient record
and if they select a date criteria then show only that patient record with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1] And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
 
D

Duane Hookom

There is no "start date" anywhere in your reply. When you place a value in
txtstartdate1 are you also placing values in txtstartdate2, txtenddate1, and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Tanweer said:
Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX Report]![txtenddate2]) OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

Duane Hookom said:
You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Tanweer said:
Please help I am stuck.
I have a form which has three field to pass and what I want to do is if
user
don't select any thing I want to shwo every thing but if they select
patient
id only show that opatient record
and if they select a date criteria then show only that patient record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1] And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I changed the query and it looks ok now however MS Access is changing the
query into another format once I save it overwriting my original Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2]) AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx Report]![cboPatient].[value])) OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2]) AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE) Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


Duane Hookom said:
There is no "start date" anywhere in your reply. When you place a value in
txtstartdate1 are you also placing values in txtstartdate2, txtenddate1, and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Tanweer said:
Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX Report]![txtenddate2]) OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

Duane Hookom said:
You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to do is if
user
don't select any thing I want to shwo every thing but if they select
patient
id only show that opatient record
and if they select a date criteria then show only that patient record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1] And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I asked a while back if this query is to supply a form or report and I don't
believe you ever answered.

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I changed the query and it looks ok now however MS Access is changing the
query into another format once I save it overwriting my original Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx Report]![cboPatient].[value]))
OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient
ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE) Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR
(((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


Duane Hookom said:
There is no "start date" anywhere in your reply. When you place a value
in
txtstartdate1 are you also placing values in txtstartdate2, txtenddate1,
and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Tanweer said:
Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one
selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also
Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX Report]![txtenddate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

:

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything
else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to do is
if
user
don't select any thing I want to shwo every thing but if they select
patient
id only show that opatient record
and if they select a date criteria then show only that patient
record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1]
And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am sorry this query is a data source for a report.

Front end has a form where user make selection to run report if they leave
all criteria blank it will select all
The Form has three criteria to Select

Patiend Name _____________________
Start Date __________ through ________
End Data ___________ through _________

Once a user make a selection or just click ok means run for all I called the
report and the report has a data source for Query which has these parameters

Thanks
Tanweer
Duane Hookom said:
I asked a while back if this query is to supply a form or report and I don't
believe you ever answered.

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I changed the query and it looks ok now however MS Access is changing the
query into another format once I save it overwriting my original Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx Report]![cboPatient].[value]))
OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient
ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE) Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR
(((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


Duane Hookom said:
There is no "start date" anywhere in your reply. When you place a value
in
txtstartdate1 are you also placing values in txtstartdate2, txtenddate1,
and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one
selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also
Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX Report]![txtenddate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

:

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything
else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to do is
if
user
don't select any thing I want to shwo every thing but if they select
patient
id only show that opatient record
and if they select a date criteria then show only that patient
record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx Report]![txtenddate1]
And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I would attempt to take the dynamic criteria out of the query and use code
to build a where clause.

Dim strWhere as String
strWhere = " 1=1 "
If Not IsNull(Me.cboPatient) Then
strWhere = strWhere & " and [PatientIS] = " & Me.cboPatient
End If
If Not IsNull(Me.txtStartDate1) Then
strWhere = strWhere & " and HOMEIVSTPDATE >=#" & _
Me.txtStartDate1 & "# "
End If
' -- other similar code
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I am sorry this query is a data source for a report.

Front end has a form where user make selection to run report if they
leave
all criteria blank it will select all
The Form has three criteria to Select

Patiend Name _____________________
Start Date __________ through ________
End Data ___________ through _________

Once a user make a selection or just click ok means run for all I called
the
report and the report has a data source for Query which has these
parameters

Thanks
Tanweer
Duane Hookom said:
I asked a while back if this query is to supply a form or report and I
don't
believe you ever answered.

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I changed the query and it looks ok now however MS Access is changing
the
query into another format once I save it overwriting my original Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]))
OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND (([Forms]![Patient
abx
Report]![cboPatient].[value]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient
ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE)
Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient
abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR
(((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


:

There is no "start date" anywhere in your reply. When you place a
value
in
txtstartdate1 are you also placing values in txtstartdate2,
txtenddate1,
and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one
selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also
Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

:

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything
else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to do
is
if
user
don't select any thing I want to shwo every thing but if they
select
patient
id only show that opatient record
and if they select a date criteria then show only that patient
record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx
Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx
Report]![txtenddate1]
And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing
wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

This is great
However the issue is I am using a Sub Report

My main Report is running based on PatiendID criteria
and Sub report is linked by Patient id and then Lab start Date and Lab End
date criteria how I will pass this Where condition to my subreport.

Thanks
Tanweer

Duane Hookom said:
I would attempt to take the dynamic criteria out of the query and use code
to build a where clause.

Dim strWhere as String
strWhere = " 1=1 "
If Not IsNull(Me.cboPatient) Then
strWhere = strWhere & " and [PatientIS] = " & Me.cboPatient
End If
If Not IsNull(Me.txtStartDate1) Then
strWhere = strWhere & " and HOMEIVSTPDATE >=#" & _
Me.txtStartDate1 & "# "
End If
' -- other similar code
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I am sorry this query is a data source for a report.

Front end has a form where user make selection to run report if they
leave
all criteria blank it will select all
The Form has three criteria to Select

Patiend Name _____________________
Start Date __________ through ________
End Data ___________ through _________

Once a user make a selection or just click ok means run for all I called
the
report and the report has a data source for Query which has these
parameters

Thanks
Tanweer
Duane Hookom said:
I asked a while back if this query is to supply a form or report and I
don't
believe you ever answered.

--
Duane Hookom
MS Access MVP
--

I changed the query and it looks ok now however MS Access is changing
the
query into another format once I save it overwriting my original Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]))
OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND (([Forms]![Patient
abx
Report]![cboPatient].[value]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient
ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE)
Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient
abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE) Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR
(((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


:

There is no "start date" anywhere in your reply. When you place a
value
in
txtstartdate1 are you also placing values in txtstartdate2,
txtenddate1,
and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one
selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add also
Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

:

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there anything
else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to do
is
if
user
don't select any thing I want to shwo every thing but if they
select
patient
id only show that opatient record
and if they select a date criteria then show only that patient
record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx
Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx
Report]![txtenddate1]
And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing
wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

I would use the "where clause" to modify the SQL property of the record
source query of the subreport.

To be honest, it is very difficult for me to debug the sql view with
multiple form control references and "And"s and "Or"s.

--
Duane Hookom
MS Access MVP
--

Tanweer said:
This is great
However the issue is I am using a Sub Report

My main Report is running based on PatiendID criteria
and Sub report is linked by Patient id and then Lab start Date and Lab
End
date criteria how I will pass this Where condition to my subreport.

Thanks
Tanweer

Duane Hookom said:
I would attempt to take the dynamic criteria out of the query and use
code
to build a where clause.

Dim strWhere as String
strWhere = " 1=1 "
If Not IsNull(Me.cboPatient) Then
strWhere = strWhere & " and [PatientIS] = " & Me.cboPatient
End If
If Not IsNull(Me.txtStartDate1) Then
strWhere = strWhere & " and HOMEIVSTPDATE >=#" & _
Me.txtStartDate1 & "# "
End If
' -- other similar code
DoCmd.OpenReport "rptYourReport", acPreview, , strWhere

--
Duane Hookom
MS Access MVP
--

Tanweer said:
I am sorry this query is a data source for a report.

Front end has a form where user make selection to run report if they
leave
all criteria blank it will select all
The Form has three criteria to Select

Patiend Name _____________________
Start Date __________ through ________
End Data ___________ through _________

Once a user make a selection or just click ok means run for all I
called
the
report and the report has a data source for Query which has these
parameters

Thanks
Tanweer
:

I asked a while back if this query is to supply a form or report and I
don't
believe you ever answered.

--
Duane Hookom
MS Access MVP
--

I changed the query and it looks ok now however MS Access is changing
the
query into another format once I save it overwriting my original
Query.
Is there any way to take cre of it.

Thanks
Tanweer


SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE (((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
((tblhomeiv.patientid)=[Forms]![Patient abx
Report]![cboPatient].[value]))
OR
(((tblhomeiv.HOMEIVSTRDATE) Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
AND
((tblhomeiv.HOMEIVSTPDATE) Between [Forms]![Patient Abx
Report]![txtenddate1]
And [Forms]![Patient ABX Report]![txtenddate2]) AND
(([Forms]![Patient
abx
Report]![cboPatient].[value]) Is Null)) OR
(((tblhomeiv.HOMEIVSTPDATE)
Between [Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient
ABX
Report]![txtenddate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient
abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTPDATE)
Between
[Forms]![Patient Abx Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE)
Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient
ABX
Report]![txtstartdate2]) AND ((tblhomeiv.patientid)=[Forms]![Patient
abx
Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR (((tblhomeiv.HOMEIVSTRDATE)
Between
[Forms]![Patient Abx Report]![txtstartdate1] And [Forms]![Patient
ABX
Report]![txtstartdate2]) AND (([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR
(((tblhomeiv.patientid)=[Forms]![Patient
abx Report]![cboPatient].[value]) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null)) OR ((([Forms]![Patient abx
Report]![cboPatient].[value]) Is Null) AND (([Forms]![Patient Abx
Report]![txtstartdate1]) Is Null) AND (([Forms]![Patient Abx
Report]![txtenddate1]) Is Null));


:

There is no "start date" anywhere in your reply. When you place a
value
in
txtstartdate1 are you also placing values in txtstartdate2,
txtenddate1,
and
txtenddate2?

I think this gives you a better view of your current query:

SELECT tblhomeiv.*, Forms![Patient abx Report]!txtstartdate1 AS
Expr2
FROM tblhomeiv
WHERE
(
patientid=[Forms]![Patient abx Report]![cboPatient].[value]
OR [Forms]![Patient abx Report]![cboPatient].[value] Is Null
)
AND
(
HOMEIVSTRDATE Between
[Forms]![Patient Abx Report]![txtstartdate1] And
[Forms]![Patient ABX Report]![txtstartdate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
)
AND
(
HOMEIVSTPDATE Between
[Forms]![Patient Abx Report]![txtenddate1] And
[Forms]![Patient ABX Report]![txtenddate2]
OR
[Forms]![Patient Abx Report]![txtstartdate1] Is Null
);



--
Duane Hookom
MS Access MVP
--

Thanks for prompt reply

This does work for a single Selection criteria
However when I put multiple criteria I am doomed

Here is the final query which not worked If I put more than one
selection
criteria it does not pull any thing
however if I just put patient id it worked however when I add
also
Start
date criteria it pull nothing.

SELECT tblhomeiv.*, tblhomeiv.patientid, tblhomeiv.HOMEIVSTRDATE,
tblhomeiv.HOMEIVSTPDATE, Forms![Patient abx Report]!txtstartdate1
AS
Expr2
FROM tblhomeiv
WHERE ((tblhomeiv.patientid=[Forms]![Patient abx
Report]![cboPatient].[value]) OR ([Forms]![Patient abx
Report]![cboPatient].[value] Is Null))
AND ((tblhomeiv.HOMEIVSTRDATE Between [Forms]![Patient Abx
Report]![txtstartdate1] And [Forms]![Patient ABX
Report]![txtstartdate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null))
AND ((tblhomeiv.HOMEIVSTPDATE Between [Forms]![Patient Abx
Report]![txtenddate1] And [Forms]![Patient ABX
Report]![txtenddate2])
OR
([Forms]![Patient Abx Report]![txtstartdate1] Is Null));

Thanks
Tanweer

:

You criteria should be something like:

Patient_ID = [Forms]![Patient abx Report]![cboPatient] Or
[Forms]![Patient
abx Report]![cboPatient] Is Null

If this query is for display in a form or report (is there
anything
else)
then I would not place any of these criteria in the query.
--
Duane Hookom
MS Access MVP
--

Please help I am stuck.
I have a form which has three field to pass and what I want to
do
is
if
user
don't select any thing I want to shwo every thing but if they
select
patient
id only show that opatient record
and if they select a date criteria then show only that patient
record
with
start date in the range selected.
Here is the for look like

Patient_id ______
Start Date ________ through ________
End Date _________ through _________


When I pass it to the Query I put in the criteria field
patient_id Is Not Null And [Forms]![Patient abx
Report]![cboPatient].[value]
in the column Start Date in the same and line I put
Is Not Null And Between [Forms]![Patient Abx
Report]![txtstartdate1]
And
[Forms]![Patient ABX Report]![txtstartdate2]
In the Column End Date in the same AND line I put
Is Not Null And Between [Forms]![Patient abx
Report]![txtenddate1]
And
[Forms]![Patient abx Report]![txtenddate2]

However I am not seeign any record coming up what I am doing
wrong

Will approeciate for any suggestion or answer.

Thanks
Tanweer
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using the OR criteria. Am I doing it right? 1
multiple criteria 4
A query for Report 5
Adding a field to a subquery 1
Dsum with multiple criteria 6
Simple Question on setting a criteria 3
QUERY MULTIPLE CRITERIA 2
Pull latest date from Multiple entries 2

Top