473,241 Members | 1,608 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,241 developers and data experts.

Using "&" and "+" in WHERE Clause

32,554 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 13619

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

Similar topics

by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've...
by: Michael Hill | last post by:
I have a general question about how people generally tend to deal with users data that they enter. As an example users enter double quotes in a text field surrounding a specific piece of text...
by: barney | last post by:
Hello, I' m using .NET System.Xml.XmlDOcument. When I do the following: XmlDocument xml = new XmlDocument(); xml.Load("blah"); .... xml.Save("blub"); I've got the problem that the following...
by: martin | last post by:
Hi, I would be extremly grateful for some help on producing an xml fragemt. The fragment that I wish to produce should look like this <Addresses> <Address>&qout;Somebody's Name&quot;...
by: Arne | last post by:
A lot of Firefox users I know, says they have problems with validation where the ampersand sign has to be written as &amp; to be valid. I don't have Firefox my self and don't wont to install it only...
by: divya | last post by:
Hi, I have a table tblbwday with 2 fields Name and Birthday.I have written this script for displaying evryday names of the people on that day. <% set objConn...
by: Ragnar | last post by:
Hi, 2 issues left with my tidy-work: 1) Tidy transforms a "&amp;" in the source-xml into a "&" in the tidied version. My XML-Importer cannot handle it 2) in a long <title>-string a wrap is...
by: Amith | last post by:
Here we are facing a problem which is when we pass a parameter to javascript putting '&' . Ex: &contact. In Japanese OS when we receive this parameter in the script '&' is converted to 'e'.So...
by: =?Utf-8?B?UmljaA==?= | last post by:
I need to build a sql string that looks like this: strSql = "Select * from tbl1 Where x In (123,456,789)" or strSql = "Select * from tbl1 Where x In (123,456,789,527,914)" The numbers...
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...

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.