CODEWITHSUNDEEP
ms-access
Radio Doc
Radio Doc

Reputation: 397

MS Access SQL - how do I conditionally set criteria to all?

I'm setting up a report that will show me personnel on each shift. I have a form with a combo box where I can select a specific shift, or by leaving it blank show all shifts. Choosing a value from the combo box mirrors that text to a hidden text box which is then passed to the query. Now, getting the report to filter by shift is the easy part, what's kicking me in the teeth right now is how do I set it so that if my Shift Filter box is empty to show all records like it would if the WHERE clause were blank?

Here's my SQL code:

SELECT DISTINCTROW tblPersonnel.EmpID
    ,tblRank.Rank
    ,tblPersonnel.NameStr
    ,tblPersonnel.Shop
    ,qryShiftRosterSub.Narrative
    ,qryShiftRosterSubShift.CurrentShift
    ,qryShiftRosterSubShift.ShopName
    ,tblRank.ID
FROM (
    (
        tblPersonnel LEFT JOIN qryShiftRosterSubShift ON tblPersonnel.EmpID = qryShiftRosterSubShift.EmpID
        ) LEFT JOIN tblRank ON tblPersonnel.Rank = tblRank.ID
    )
LEFT JOIN qryShiftRosterSub ON tblPersonnel.EmpID = qryShiftRosterSub.EmpID
WHERE (
        ((qryShiftRosterSubShift.CurrentShift) = IIf(Len([Forms] ! [frmNavMain] ! [NavigationSubform] ! [ShiftFilter]) = 0, 'Is Not Null', [Forms] ! [frmNavMain] ! [NavigationSubform] ! [ShiftFilter]))
        AND ((tblPersonnel.DeleteFlag) = False)
        );

I've got a few queries that are chained together and this is the last one before the completed dataset is sent to the report. Like I said, I can get it to show me just a specific shift easily, and by clearing the criteria from CurrentShift I can get it to show all records, but how do I get it to swap between the two based on what's in my filter box?

Upvotes: 0

Views: 46

Answers (3)

Erik A
Erik A

Reputation: 32642

You can just add an OR clause to check if the combo box is empty. Note that you both need to account for "" empty strings, and Null values. I prefer to check using Nz(MyComboBox) = ""

Implementation:

SELECT DISTINCTROW tblPersonnel.EmpID
    ,tblRank.Rank
    ,tblPersonnel.NameStr
    ,tblPersonnel.Shop
    ,qryShiftRosterSub.Narrative
    ,qryShiftRosterSubShift.CurrentShift
    ,qryShiftRosterSubShift.ShopName
    ,tblRank.ID
FROM (
    (
        tblPersonnel LEFT JOIN qryShiftRosterSubShift ON tblPersonnel.EmpID = qryShiftRosterSubShift.EmpID
        ) LEFT JOIN tblRank ON tblPersonnel.Rank = tblRank.ID
    )
LEFT JOIN qryShiftRosterSub ON tblPersonnel.EmpID = qryShiftRosterSub.EmpID
WHERE (
        ((qryShiftRosterSubShift.CurrentShift) = IIf(Len([Forms] ! [frmNavMain] ! [NavigationSubform] ! [ShiftFilter]) = 0, 'Is Not Null', [Forms] ! [frmNavMain] ! [NavigationSubform] ! [ShiftFilter])
        OR Nz([Forms] ! [frmNavMain] ! [NavigationSubform] ! [ShiftFilter]) = "")
        AND ((tblPersonnel.DeleteFlag) = False)
        );

Upvotes: 1

Sergey S.
Sergey S.

Reputation: 6336

You can try to use condition like this:

qryShiftRosterSubShift.CurrentShift = [Forms]![frmNavMain]![NavigationSubform]![ShiftFilter]
OR  Len(Nz([Forms]![frmNavMain]![NavigationSubform]![ShiftFilter],""))= 0

Upvotes: 1

jetblack
jetblack

Reputation: 155

Why don't you append the 'WHERE' part of the SQL just if your combobox is not empty?

Upvotes: 0

Related Questions