Query Criteria - not working ?

[bdsii]

I am using a form with 10 fields used as a search form. The idea is for the user to enter data into any or all of the 10 fields to search the database. If they leave the field blank, the search should not limit the returned data based on that blank field. I am using a form to pass the search parameters to a query. The criteria line in the code has a statement like the following:

Like [Forms].[frmSearch].[txtCaseNumber] & "*"


This allows the user to enter a wildcard search for partial entries which is very helpful.

This all works properly.....EXCEPT when any of the search fields have no data in the database. For instance, if one of the 10 fields is missing data in the database (that field is null), then that record is overlooked for some reason.

I have tried code to set each control on the form to blank or null.....neither works to solve this problem.
Code to try to set the form control to NULL

txtCaseNumber.Value = Null


Code to try to set the form control to blank

txtCaseNumber.Value = ""


What I would like to happen is for the query to return the records that match the entered search parameters entered into the form and not limit the returned records if any of the search fields is blank or null.

Anyone have any ideas ??? :banghead:


Thanks !

[hansup]

I'm not sure I understand your description, so let me show you one which may be similar.

I have a table named MyTable which includes a column named "names". These are the values of the names column for the 6 rows in the table.

names
Abe
Betty
Carl
Adelle

David

Just to be clear, the value in the fifth row (the row after Adelle) is Null.

I created a form named frmSearchMyTable with a text box named txtNames. Then I created this query.

SELECT m.names
FROM MyTable AS m
WHERE
(((m.names) Like [forms]![frmSearchMyTable]![txtNames] & "*"))
OR ((([forms]![frmSearchMyTable]![txtNames]) Is Null))
ORDER BY m.id;

Then if I type an A in the txtNames text box and run the query, I get 2 rows: Abe and Adelle.

If I type a Z in txtNames, I get no matching rows.

If I delete any value in txtNames, I get all 6 rows.

Is that what you're trying to accomplish?

One other point which may or may not help. Consider this line:

Debug.Print Null Like "*"

Ordinarily you would expect a Like comparison to return True or False. In this case, you might expect it to return False. However, instead of False, it will return Null.

The reason for that is Null is an unknown value. Just as Null can never be equal to anything (even another Null), it can never be Like anything (even another Null).

[bdsii]

Thanks hansup for the reply.....not sure if this is apples to apples......since I have 10 different fields to search upon (say Names, Address, zip, phone, bday, etc) and you enter search criteria for Names but the zip has not been entered for one of your records in the database, would that record with the blank zip be returned in your query ? Does this help any or make it more confusing ?? aaaggghhh !! :-)

[hansup]

Guess I'm more lost now, bdsii. My thought was if user enters a search value in Zip text box, query should limit rows to only those whose Zip values match the search criterion. But if user doesn't include a search value for Zip, don't apply any filtering based on Zip to the query results.

To me the logic would be the same as I showed you for Names.

If you have 10 different search criteria, your query would need to incorporate all 10.

SELECT * FROM YourTable
WHERE
condition1
AND condition2
AND condition3
AND condition4
AND condition5
AND condition6
AND condition7
AND condition8
AND condition9
AND condition10

In the example I showed you, condition1 was:
(((m.names) Like [forms]![frmSearchMyTable]![txtNames] & "*"))
Or ((([forms]![frmSearchMyTable]![txtNames]) Is Null))

Granted it will be challenging to add in the other 9 conditions, but that's the approach you chose. It's not the approach I would choose for myself.

I would build a continuous form based on a SELECT statement.
SELECT [field names] FROM MyTable ORDER BY [some expression];
Then include my unbound search criteria text boxes in the form header. And build a new SELECT which includes any of those search values the user has supplied:

Dim strWhere As String
Dim strSql As String
Dim strFieldList As String
Dim strOrderBy As String
If Not IsNull(Me.txtNames) Then
strWhere = strWhere & " And m.names Like '" & _
Replace(Me.txtNames, "'", "''") & "*'"
End If
If Not IsNull(Me.txtZip) Then
strWhere = strWhere & " And m.zip Like '" & _
Replace(Me.txtZip, "'", "''") & "*'"
End If
strFieldList = "*" 'adjust to taste
strOrderBy = "m.id" 'adjust to taste
strSql = "SELECT " & strFieldList & " FROM MyTable"
IF Len(strWhere) > 0 Then
'discard first " And " from strWhere
strWhere = Mid(strWhere, 6)
strSql = strSql & " WHERE " & strWhere
End If
strSql = strSql & " ORDER BY " & strOrderBy
Debug.Print strSql
Me.RowSource = strSql

I haven't tested that code. Something close to that should work.

[bdsii]

thanks hansup for the code and information....will give it a look and see what I can do ! :-)