parameter query "leave blank for all" 1

[sawilliams]

I want to be able to query out data about actions. I have a form with three combo boxes that allows the user to pick "Assigned by", "Assigned to" and "Status". Using the code:

PARAMETERS [forms]![frmActionQuery]![AssignedTo] Text ( 255 ), [forms]![frmActionQuery]![AssignedBy] Text ( 255 ), [forms]![frmActionQuery]![ActionStatus] Text ( 255 );
SELECT tblActions.AffiliateID, tblActions.ActionName, tblActions.ActionType, tblActions.ActionDate, tblActions.ActionDueDate, tblActions.AssignedTo, tblActions.AssignedBy, tblActions.ActionStatus
FROM (tblActions LEFT JOIN tblID ON tblActions.AffiliateID = tblID.AffiliateID) LEFT JOIN tblSalutation ON tblActions.AffiliateID = tblSalutation.AffiliateID
WHERE (((tblActions.AssignedTo)=[forms]![frmActionQuery]![AssignedTo]) AND ((tblActions.AssignedBy)=[forms]![frmActionQuery]![AssignedBy]) AND ((tblActions.ActionStatus)=[forms]![frmActionQuery]![ActionStatus])) OR (((tblActions.AssignedTo)=[forms]![frmActionQuery]![AssignedTo]) AND ((tblActions.AssignedBy)=[forms]![frmActionQuery]![AssignedBy]));

I am able to leave "Status" blank and get ALL entries regardless of status, but I want to be able to leave any of the three blank (or all) if I just want to see ALL the Actions assigned to one person regardless of who assigned it or what the status is.

 

[PHV]

WHERE (tblActions.AssignedTo=[forms]![frmActionQuery]![AssignedTo] OR [forms]![frmActionQuery]![AssignedTo] Is Null)
AND (tblActions.AssignedBy=[forms]![frmActionQuery]![AssignedBy] OR [forms]![frmActionQuery]![AssignedBy] Is Null)
AND (tblActions.ActionStatus=[forms]![frmActionQuery]![ActionStatus] OR [forms]![frmActionQuery]![ActionStatus] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

PHV:

I actually tried that before but nonetheless pasted your code in and tried again. But if I leave my combo box on my form blank (for all) and run the query, I get no rows returned from the query. However, if I select something from all three combo boxes, I get correct results.

Perplexed...

 

[PHV]

So, the combos are blank, not null ?
WHERE (tblActions.AssignedTo=[forms]![frmActionQuery]![AssignedTo] OR Trim([forms]![frmActionQuery]![AssignedTo] [tt]& '')='')[/tt]
AND (tblActions.AssignedBy=[forms]![frmActionQuery]![AssignedBy] OR Trim([forms]![frmActionQuery]![AssignedBy] [tt]& '')='')[/tt]
AND (tblActions.ActionStatus=[forms]![frmActionQuery]![ActionStatus] OR Trim([forms]![frmActionQuery]![ActionStatus] [tt]& '')='')[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sawilliams]

PH:

Duh...

Null... blank...

It's late in the day and my brain is null AND blank. Thanks for your quick, helpful response.

sawilliams