Search Form Variables

[DBL]

I have a search form that has 14 unbound field. As little or as many of these fields can be selected from to widen or narrow the search criteria. I have 6 text box fields that the user can enter a search term, one word in each box, which searches a certain field in the database.

I've been using the where statement to string together the variables and search a query for the results before exporting to Excel:

If Not IsNull(Me.Combo6) Then
where = where & " AND [AuthName] = '" + Me![Combo6].Column(1) & "'"
End If

If Not IsNull(Me.Combo31) Then
where = where & " AND [DCategory] = '" + Me![Combo31].Column(1) & "'"
End If

If Not IsNull(Me.Combo33) Then
where = where & " AND [Path1] = '" + Me![Combo33].Column(1) & "'"
End If

If Not IsNull(Me!Text35) Then
where = where & " AND [PMPlace1] like '" + "*" + Me![Text35] + "*" + "'"
End If

If Not IsNull(Me!Text8) Then
where = where & " AND [cdcause] like '" + "*" + Me![Text8] + "*" + "'"
End If

This works well until I try and bring in any of the text fields (text 8 is the first one) If I try and run the query with a second item in the second text field [text10] then the query shows the first critera row correctly but the second text field item is in an Or criteria row on it's own - so it gives me records without the other criteria in place.

I've tried using:
If Not IsNull(Me![Text10]) Then
where = where & " or[PMDate] Between " + F + " And " + T _
& " and[AuthName] = '" + Me![Combo6].Column(1) & "'" + "" _
& " and [Path1] = '" + Me![Combo33].Column(1) & "'" + "" _
& " and [DCategory] = '" + Me!Combo31.Column(1) & "'" + "" _
& " and [PMPlace1] Like '" + "*" + Me![Text35] + "*" + "'" _
& " and [CDCause] Like '" + "*" + Me![Text10] + "*" + "'"

as the search string for the Or row in the query which works but only if you select a search criteria for the other fields too. If you leave one of those blank I just get a syntax error.

Is there a better way of pulling these variables out into a query?

 

[SimonB1978]

Hi,

Here is how I would do this:

define another variable for the text fields criteria (for clarity), say strTextCriteria. Then:

If Not IsNull(Me!Text8) Then
strTextCriteria= strTextCriteria & "[cdcause] like '" + "*" + Me![Text8] + "*" + "'"
End If

If Not IsNull(Me!Text10) Then
strTextCriteria= strTextCriteria & " OR [cdcause] like '" + "*" + Me![Text10] + "*" + "'"
End If

If Not IsNull(Me!Text12) Then
strTextCriteria= strTextCriteria & " OR [cdcause] like '" + "*" + Me![Text12] + "*" + "'"
End If

...

where = where & " AND (" & strtextCriteria & ")"

basically what you want is Combo1 AND Combo2 AND Combo3 .... AND (Text1 OR Text2 OR...)

By the way, you should try to be consistant and user either & or + to concatenate but not a mix of both.

Simon B.

 

[HiTechCoach]

Hi,

Here is how I would do this:

define another variable for the text fields criteria (for clarity), say strTextCriteria. Then:

If Not IsNull(Me!Text8) Then
strTextCriteria= strTextCriteria & "[cdcause] like '" + "*" + Me![Text8] + "*" + "'"
End If

If Not IsNull(Me!Text10) Then
strTextCriteria= strTextCriteria & " OR [cdcause] like '" + "*" + Me![Text10] + "*" + "'"
End If

If Not IsNull(Me!Text12) Then
strTextCriteria= strTextCriteria & " OR [cdcause] like '" + "*" + Me![Text12] + "*" + "'"
End If

...

where = where & " AND (" & strtextCriteria & ")"

basically what you want is Combo1 AND Combo2 AND Combo3 .... AND (Text1 OR Text2 OR...)

By the way, you should try to be consistant and user either & or + to concatenate but not a mix of both.

Simon B.

Simon,

You said:

By the way, you should try to be consistant and user either & or + to concatenate but not a mix of both.

I hate to disagree with you , but that is not true since they are not equal. The + does not work the exact same way as the &. I do have to use a "mix of both" then when working with the parts of names and address.

I have found the you should normally use the &, not the +. If your data can have Nulls, then depending on how you want to control what happens with the Nulls, which one, or both, you will want to use.

Law of Propagating Nulls (LOPN)
---------------------------------------
When working with Null values in expressions, whether in VBA, a control source property or an expression column of a query object, it is important to know how a Null value behaves within an expression as well as how a Null value affects the final result of an expression. The behavior of Null is especially good to know, when using the two valid VBA operators for text concatenation, the plus (+) and the ampersand (&).

In short, Null will propagate throughout an expression until you have an operator that prevents it. This behavior is known as the "Law of Propagating Nulls" (LOPN).


Analogy
----------
Some folks have made the analogy that working with Null is like multiplying by Zero. We know that anything multiplied by 0 equals 0. In a similar vein, anything "mathematically" operated with a Null yields a Null. I use the term mathematically in quotes simply because in Access/VBA expressions, the addition (+) operator (typically thought of as a "mathematical" operator) is a dual purpose operator. It will add numbers, as well as "add" (or concatenate) text. Access/VBA also has another concatenation operator, the ampersand (&). But as seen above in the examples, when you "add" (+) text with a Null, the result is a Null, but when you concatenate (&) a Null with text, the result is that text. Therefore the ampersand is an operator that prevents Null propogation.

From the detailed discussion with examples: Nulls and their behavior

 

[DBL]

That's brilliant, thank you. I had finally managed to get it to work with about 100 rows of code and variables and now it's much neater!

Thank you.

 

[SimonB1978]

Simon,

You said:



I hate to disagree with you , but that is not true since they are not equal. The + does not work the exact same way as the &. I do have to use a "mix of both" then when working with the parts of names and address.

I have found the you should normally use the &, not the +. If your data can have Nulls, then depending on how you want to control what happens with the Nulls, which one, or both, you will want to use.


From the detailed discussion with examples: Nulls and their behavior

Thanks a lot! I didn't know about that!!! It just made more sense to me (for readability) to use the same operator everywhere! I can certainly use that somewhere!

Simon B.