Using Combo boxes as search criteria ? (Allen Browne - Search code)

[baronqueefington]

Hello again,

I have created a search form based upon Allen Brownes search code - http://www.allenbrowne.com/ser-62.html

I have it up and running and the text search box's work brilliantly, but I now wish to add a combo box into the criteria string, which is causing me problems. My code is

If Not IsNull(Me.cboBiaName) Then
strWhere = strWhere & "([BIA Name] = "" & Me.cboBiaName & "") AND "
End If

I have the combo box in the header, which when clicked shows the correct list, but once a criteria is selected search brings back no records. If I leave it blank and enter criteria in the other text search boxes the search runs correctly.

The database is made up of 3 tables a main table, which the search form is generated from and 2 list tables.

[June7]

What is the RowSource of the combobox? Does it include an ID field? Is the actual value of combobox the ID?

Have you set lookups with alias in table? http://access.mvps.org/access/lookupfields.htm What data type is [BIA Name]?

If [BIA Name] is a text field and actually has descriptive text value (not ID) then look at Allen's code again for text type field. It doubles the quote mark for text delimiter. An alternative is an apostrophe.

strWhere = strWhere & "([BIA Name] = '" & Me.cboBiaName & "') AND "

[baronqueefington]

Thank you! Replacing two of the quotations and replacing them with apostrophe's solved it! Are you able to explain why that did it?

Thanks again.

[June7]

If you want a quote mark to be recognized as a literal character between two other quote marks, the quote mark must be doubled. This is called escaping a special character. Sometimes doubling a special character will escape it, sometimes using a \ before a special character will escape it. This concept is common to all programming languages. However, the \ won't work for constructing an SQL statement in VBA. An apostrophe between quotes will be recognized as a text delimiter as long as they are in pairs - one on each side of the text parameter. Solo apostrophes such as can be found in some data (O'Conner, O'Hare, etc) can cause issue. Say you wanted to use LastName as filter criteria and it is possible some names will have an apostrophe, need to manage that possibility.

strWhere = strWhere & "([LastName] = '" & Replace(Me.cboLastName, "'", "''") & "') AND "