Form Filtered Query

[Guest]

I have a form filtered query where I am using 2 combo boxes to allow the user
to select data from the boxes and then run a query. I am having a problem in
that if the user does not select a value in BOTH boxes, the query returns no
results. I want the query to return all results if one or both boxes are left
blank.

 

[Tom Ellison]

Dear AL:

Likely what you could use is:

WHERE (Column1 = Forms!FormName!ControlName1
OR Nz(Forms!FormName!ControlName, "") = "")
AND (Column2 = Forms!FormName!ControlName2
OR Nz(Forms!FormName!ControlNamd, "") = "")

Put this into the SQL of your query at the appropriate place. If that is
hard for you, copy the SQL of the query into your next post and I'll show
you.

Tom Ellison

 

[Guest]

Thanks Tom, Im a pretty new user so here is my SQL from the query:

SELECT Victim.VictimLast, Victim.VictimFirst, Victim.Attorney,
Perps.PerpLast, Perps.PerpFirst, Victim.Basket, Victim.[Co-Counsel
Recommendation], Victim.Rationale
FROM Perps INNER JOIN (Victim INNER JOIN Junction ON Victim.VictimID =
Junction.VictimID) ON Perps.PerpID = Junction.PerpID
WHERE (((Victim.Attorney)=[Forms]![Type]![cmbAttorney]) AND ((Victim.[Type
of Claim])=[Forms]![Type]![cmbType]));

Thanks for your help

 

[Tom Ellison]

Dear AL:

SELECT V.VictimLast, V.VictimFirst, V.Attorney, P.PerpLast,
P.PerpFirst, V.Basket, V.[Co-Counsel Recommendation], V.Rationale
FROM Perps P
INNER JOIN (Victim V
INNER JOIN Junction J
ON V.VictimID = J.VictimID)
ON P.PerpID = J.PerpID
WHERE (V.Attorney = [Forms]![Type]![cmbAttorney]
OR Nz([Forms]![Type]![cmbAttorney], "") = "")
AND (V.[Type of Claim] = [Forms]![Type]![cmbType]
OR Nz([Forms]![Type]![cmbType], "") = "");

I've introduced aliases V for Victim, J for Junction, and P for Perp to keep
the length down.

Please let me know if this works for you.

Tom Ellison

Thanks Tom, Im a pretty new user so here is my SQL from the query:

SELECT Victim.VictimLast, Victim.VictimFirst, Victim.Attorney,
Perps.PerpLast, Perps.PerpFirst, Victim.Basket, Victim.[Co-Counsel
Recommendation], Victim.Rationale
FROM Perps INNER JOIN (Victim INNER JOIN Junction ON Victim.VictimID =
Junction.VictimID) ON Perps.PerpID = Junction.PerpID
WHERE (((Victim.Attorney)=[Forms]![Type]![cmbAttorney]) AND ((Victim.[Type
of Claim])=[Forms]![Type]![cmbType]));

Thanks for your help

Dear AL:

Likely what you could use is:

WHERE (Column1 = Forms!FormName!ControlName1
OR Nz(Forms!FormName!ControlName, "") = "")
AND (Column2 = Forms!FormName!ControlName2
OR Nz(Forms!FormName!ControlNamd, "") = "")

Put this into the SQL of your query at the appropriate place. If that is
hard for you, copy the SQL of the query into your next post and I'll show
you.

Tom Ellison