It would be possible to modify the SQL View of the query so that it returns
True where the control is null, True where the control is "All", or a match
where the control has another value:
WHERE (([forms]![main]![oppty id] Is Null)
OR ([forms]![main]![oppty id] = "All")
OR ([SomeField] = [forms]![main]![oppty id]))
However, this kind of thing will be way to unweildy and inefficient for 20
drop-downs. An efficient solution would be to build the WHERE clause from
only those boxes that have a value. You could leave the criteria out of the
query all together, and instead build up a string to use as the Filter for
your form, or the WhereCondition for OpenReport. If necessary, you can build
the entire SQL statement and assign it to the RecordSource of your form or
report, or even the SQL property of the QueryDef.
The code below shows how to build up the WHERE string from the non-blank
boxes. You can adapt it so it ignores "ALL" in your combos as well. It
illustrates how to use the " character to delimit strings, and the #
character for dates (as well as explicitly formatting them so they work in
all countries.)
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Dim strSql As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
'Text field example.
If Not IsNull(Me.txtFiilterSurname) Then
strWhere = strWhere & "([Surname] = """ & _
Me.txtFiilterSurname & """) AND "
End If
'Date field example
If Not IsNull(Me.txtFilterBirthDate) Then
strWhere = strWhere & "([BirthDate] = " & _
Format(Me.txtFilterBirthDate, conJetDate) & ") AND "
End If
'Number field example
If Not IsNull(Me.txtFilterAmount) Then
strWhere = strWhere & "([Amount] = " & _
Me.txtFilterAmount & ") AND "
End If
'etc for other controls.
'Now chop off the trailing " AND ".
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
'Debug.Print strWhere
End If
'Finally apply as the filter to the form.
If Me.Dirty Then
Me.Dirty = False
End If
Me.Filter = strWhere
Me.FilterOn = True
'Or, apply to a report
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
'Or, build the full query statement
strSql = "SELECT * FROM Table1 WHERE " & strWhere & " ORDER BY Field1;"
'and apply to a query
CurrentDb.QueryDefs("Query1").SQL = strSql
'or a form
Me.RecordSource = strSql
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I'm creating an Access database that will function as a query builder
> through a Forms interface. I have approximately 20 dropdowns which
> will allow users to select their criteria, and then a command button
> that will run a query. The unbound dropdowns default to the text "All"
> which, I hope, will act as if there is no criteria in the query. I
> tried an IIF stamement like this:
>
> IIF([forms]![main]![oppty id]="All","*",[forms]![main]![oppty id])
>
> This does not return the correct results. I want the True part of the
> stament to return all values, whether null or otherwise. (I.e. as if
> there was no criteria in that query field at all.) Does anyone know
> how to do this?
>
> Second question...
>
> I have checkboxes next to each of the 20 criteria fields. The
> intention for these checkboxes is to change the visible property of the
> field once the query is run. In other words, if the User puts a check
> next to the field "Oppty Id" it will appear once the query is run. If
> there is no check, the fields visible property will remain False. Is
> this possible to do in a query? (I know it can be done in a report).
> Any ideas would be appreciated!
Similar Threads
