To prepare a WHERE clause for multiple field selection, but to ignore any fields where the selection criteria are not set. ONLY WORKS WITH TEXT FIELD SELECTIONS.
Scenario :
You have a table (tblMember) containing information for various people.
Table Name=tblMember
Expand|Select|Wrap|Line Numbers
- Field; Type; IndexInfo
- MemberID; AutoNumber; PK
- Surname; String
- Forenames; String
- ...
You are looking simply to prepare a WHERE clause to run a query, although the same string could be used as a filter to a report, form or subform if required.
Concept :
In string manipulation you can use either "&" or "+" to concatenate strings. The difference is that
STRING & Null == STRING
STRING + Null == Null
NB. Empty controls on a form will always return a Null value.
Usage :
The following code creates a string (strWhere) formatted with either or both of the fields - depending on what's been entered on the form.
The stub of the string is simply "(TRUE)" and any other parts are only added if the related TextBox field is NOT Null.
We do this by using "&" between the major elements but "+" within them.
Expand|Select|Wrap|Line Numbers
- Private Sub cmdSearch_Click()
- Dim strWhere As String, strSQL As String
- 'If nothing added then "(TRUE)" will allow all records.
- 'If either of the TextBox fields are Null then ...
- 'the whole line of code they are included on will resolve to Null.
- strWhere = "(TRUE" & _
- " AND ([Forenames] Like '*" + Me.txtForenames + "*')" & _
- " AND ([Surname] Like '*" + Me.txtSurname + "*')" & _
- ")"
- 'We add this into a SELECT query for the table...
- strSQL = "SELECT * " & _
- "FROM [tblMember] " & _
- "WHERE " & strWhere
- ...
- End Sub
Expand|Select|Wrap|Line Numbers
- Debug.Print "(TRUE" & _
- " AND ([Forenames] Like '*" + Null + "*')" & _
- ")"
- (TRUE)