SELECT specific via filter or select all if no filter

[flect]

Howdy accessians -

I have 2 text boxes on my main navigation page - first box selects a country - and the second box selects a report from that country.

The second box has a query that is dependant on something being selected in the first box

everything is currently working fine - but what I would like to do is be able to show and select from ALLthe reports in the second box if there is nothing selected in the country box.

ie*if txtboxA is null, SELECT * from tbl, else SELECT * WHERE agentID = forms!frmnavigation!masterfilter

SELECT tblReports.ReportID, tblAgency.AgencyName, tblReports.PeriodType, tblReports.PeriodEnded, tblReports.Completed
FROM tblAgency INNER JOIN tblReports ON tblAgency.AgencyID = tblReports.AgencyID
WHERE (((tblReports.AgencyID)=[forms]![frmnavigation]![masterfilter]))
ORDER BY tblReports.ReportID DESC;

Obviously if I remove the WHERE i get exactly what i need...

is there some way to do a conditional WHERE?

ie, can i use IIF in the criteria secion of the query builder?

I have a feeling that I'm missing something fundamental.

 

[namliam]

Check out this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=153319

 

[flect]

Thanks all - that thread was *kind of* what i was after but not the final solution.

I ended up creating another query without the WHERE and then put a bit of code to select one query or the other.

Private Sub MasterFilter_AfterUpdate()
If Forms!frmnavigation!MasterFilter = 0 Then
Me.MasterReportFilter.RowSource = "ReportQryAll"
Else
Me.MasterReportFilter.RowSource = "ReportQry"
End If
Me.Form.Refresh
Me.Form.Requery
End Sub

 

[namliam]

Not a perfect solution IMHO, but if it works, it works

You should really indent your code tho... makes it very much more readable.

Private Sub MasterFilter_AfterUpdate()
    If Forms!frmnavigation!MasterFilter = 0 Then
        Me.MasterReportFilter.RowSource = "ReportQryAll"
    Else
        Me.MasterReportFilter.RowSource = "ReportQry"
    End If
    Me.Form.Refresh
    Me.Form.Requery
End Sub

 

[sqlbot2000]

SELECT tblReports.ReportID, tblAgency.AgencyName, tblReports.PeriodType, tblReports.PeriodEnded, tblReports.Completed
FROM tblAgency INNER JOIN tblReports ON tblAgency.AgencyID = tblReports.AgencyID
[B]WHERE 
(
    (
        [forms]![frmnavigation]![masterfilter] = 0
    )
    OR
    (
        tblReports.AgencyID = [forms]![frmnavigation]![masterfilter]
    )
)[/B]
ORDER BY tblReports.ReportID DESC;

 

[Brianwarnock]

The conditional Where mentioned in post1 would be achieved by

SELECT .. [fldCountry]=[forms]![form1]![Countryname] Or [forms]![form1]![Countryname] Is Null AS Expr1
FROM table
WHERE ((([fldCountry]=[forms]![form1]![Countryname] Or [forms]![form1]![Countryname] Is Null =True));


Brian