Filter based on OptionBoxes

  • Thread starter Thread starter Brad
  • Start date Start date
B

Brad

Thanks for taking the time to read my question.

I have two Option Boxes. One filters records based on History status (yes or
no) and the other filter SHOULD filter based on Status (Active or Cancelled).

My query worked fine until I tried to add the Status to the query. I don't
have my filter logic quite right and so when I try to filter Status for
anything / all / no filter, my query doesn't work, but if I filter for only
Active (4), then it works. Any ideas on how I can get this to work?

Thanks,

Brad

SQL:
SELECT tblCellInfoDetail.CellDetailID, tblCellInfo.CellNumber,
tblCellInfoDetail.Status, tblCellInfoDetail.Historical,
tblCellInfoDetail.tblCellInfoID, tblCellInfoDetail.ContractExpiryDate,
IIf([Status]="Cancelled",False,True) AS StatusCheck
FROM tblCellInfo INNER JOIN tblCellInfoDetail ON tblCellInfo.CellID =
tblCellInfoDetail.tblCellInfoID
WHERE
(((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=1,IIf([Historical]=True,1,0),0))=1)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=2,IIf([Historical]=False,2,0),0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=3,3,0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraShowHideCancelled]=2,IIf([Status]<>"Cancelled",4,0),0))=4))
OR
(((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=1,IIf([Historical]=True,1,0),0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=2,IIf([Historical]=False,2,0),0))=2)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=3,3,0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraShowHideCancelled]=2,IIf([Status]<>"Cancelled",4,0),0))=4))
OR
(((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=1,IIf([Historical]=True,1,0),0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=2,IIf([Historical]=False,2,0),0))=0)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraHistShowHide]=3,3,0))=3)
AND
((IIf([Forms]![frmSwitchboard]![frmMaintCellInfo].[Form]![frmMaintCellInfoDetail].[Form]![fraShowHideCancelled]=2,IIf([Status]<>"Cancelled",4,0),0))=4));
 
Back
Top