By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,694 Members | 2,015 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Using "&" and "+" in WHERE Clause

NeoPa
Expert Mod 15k+
P: 31,277
Intention :
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
  1. Field; Type; IndexInfo
  2. MemberID; AutoNumber; PK
  3. Surname; String
  4. Forenames; String
  5. ...
You have a form (frmMemberSearch) with two TextBox controls (txtSurname & txtForenames) for selecting records. You would like the user to be able to enter as much or as little information as they know. This may be full or partial items, where either or both fields are used. A command button (cmdSearch) triggers the code to prepare and start the search.
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
  1. Private Sub cmdSearch_Click()
  2.   Dim strWhere As String, strSQL As String
  3. 'If nothing added then "(TRUE)" will allow all records.
  4. 'If either of the TextBox fields are Null then ...
  5. 'the whole line of code they are included on will resolve to Null.
  6.   strWhere = "(TRUE" & _
  7.              " AND ([Forenames] Like '*" + Me.txtForenames + "*')" & _
  8.              " AND ([Surname] Like '*" + Me.txtSurname + "*')" & _
  9.              ")"
  10.   'We add this into a SELECT query for the table...
  11.   strSQL = "SELECT * " & _
  12.            "FROM [tblMember] " & _
  13.            "WHERE " & strWhere
  14.   ...
  15. End Sub
As an illustration, copy the following code into the Immediate window of your debugger (Alt-F11 from Access then Ctrl-G) and notice the string that's printed is simply "(TRUE)". The whole middle line (between the two "&"s) has resolved to Null - and therefore disappeared from the resultant string.
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "(TRUE" & _
  2.              " AND ([Forenames] Like '*" + Null + "*')" & _
  3.              ")"
  4. (TRUE)
NB. This concept can also be used to vacate the whole WHERE clause of a SQL string if required. Be careful not to leave extraneous "AND"s in any resultant string though.
Jan 22 '08 #1
Share this Article
Share on Google+