471,073 Members | 1,165 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 471,073 developers and data experts.

Using "&" and "+" in WHERE Clause

32,341 Expert Mod 16PB
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 == 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
0 12795

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by ian justice | last post: by
4 posts views Thread by barney | last post: by
5 posts views Thread by martin | last post: by
14 posts views Thread by Arne | last post: by
1 post views Thread by Amith | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.