Multi field search form all results not showing

[Ruegen]

I just realised my multi textbox field search form isn't showing all the results because some are null.

I want to show the results in the text boxes so I use Like "*" & & "*" in the criteria that points to the corresponding textbox however it won't show nulls

If I put an OR IS null that totally messes with the query for me and I get those results when I don't want them



how to work around this problem as some fields will be null...?

[pradeep.sands]

In the WHERE/Filter condition, where you mention your LIKE condition, also add this to it:

Len(columnNameContainingNulls)=0

I checked it with an example..it works for me...
IsNull or IsEmpty isnt working...but Len(xxx)=0 is working...

[Ruegen]

In the WHERE/Filter condition, where you mention your LIKE condition, also add this to it:

I checked it with an example..it works for me...
IsNull or IsEmpty isnt working...but Len(xxx)=0 is working...

so Expre1: Len([tblSchools].[SchoolEmail])

Criteria 0

This does add more values to the query but it messes with the search on the form...

[Ruegen]

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolPostCode, tblSchools.SchoolSuburb, tblStates.SchoolState, tblSchools.SchoolPhone, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.StateIDFROM tblStates INNER JOIN tblSchools ON tblStates.[StateID] = tblSchools.[StateID]
WHERE (((tblSchools.SchoolName) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolS] & "*") AND ((tblSchools.SchoolPostCode) Like "*" & [Forms]![frmSchoolSearch]![txtFilterSchoolPostCode] & "*") AND ((tblSchools.SchoolSuburb) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolSub] & "*") AND ((tblSchools.SchoolPhone) Like "*" & [Forms]![frmSchoolSearch]![txtPhone] & "*") AND ((tblSchools.SchoolEmail) Like "*" & [Forms]![frmSchoolSearch]![txtEmail] & "*") AND ((tblSchools.[1ContactSurname]) Like "*" & [Forms]![frmSchoolSearch]![txtSurname] & "*") AND ((tblSchools.StateID)=Nz([Forms]![frmSchoolSearch]![cmbState],[tblSchools]![StateID])))
ORDER BY tblSchools.NewSchoolsID;

[Ruegen]

bump, anyone?

[ssanfu]

It depends on your search form design. Right now, it looks like you HAVE to enter something in all 5 search boxes.
You can try this query:

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolPostCode, tblSchools.SchoolSuburb, tblSchools.SchoolPhone, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.StateID, tblStates.SchoolState
FROM tblStates INNER JOIN tblSchools ON tblStates.[StateID] = tblSchools.[StateID]
WHERE (((tblSchools.SchoolName) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolS] & "*" OR (tblSchools.SchoolName is null)) AND 
((tblSchools.SchoolPostCode) Like "*" & [Forms]![frmSchoolSearch]![txtFilterSchoolPostCode] & "*" OR (tblSchools.SchoolPostCode is null)) AND 
((tblSchools.SchoolSuburb) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolSub] & "*" OR (tblSchools.SchoolSuburb is null)) AND 
((tblSchools.SchoolPhone) Like "*" & [Forms]![frmSchoolSearch]![txtPhone] & "*" OR (tblSchools.SchoolPhone is null)) AND 
((tblSchools.SchoolEmail) Like "*" & [Forms]![frmSchoolSearch]![txtEmail] & "*" OR (tblSchools.SchoolEmail is null)) AND 
((tblSchools.[1ContactSurname]) Like "*" & [Forms]![frmSchoolSearch]![txtSurname] & "*" OR (tblSchools.[1ContactSurname] is null)) AND 
((tblSchools.StateID)=Nz([Forms]![frmSchoolSearch]![cmbState],[tblSchools]![StateID]))) 
ORDER BY tblSchools.NewSchoolsID;

Is this a saved query or is it in the record source of the form?

I can think or 2 or 3 methods that might work for your situation depending on your design. Would you explain how you are searching? Forms, buttons text boxes or combo boxes,....

[amrut]

AND ((tblSchools.StateID)=Nz([Forms]![frmSchoolSearch]![cmbState],[tblSchools]![StateID])))

Have you tested the query without the "StateID" criteria ?If the [Forms]![frmSchoolSearch]![cmbState] is not null, are the results correct ?

[Ruegen]

It depends on your search form design. Right now, it looks like you HAVE to enter something in all 5 search boxes.
You can try this query:

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolPostCode, tblSchools.SchoolSuburb, tblSchools.SchoolPhone, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.StateID, tblStates.SchoolState
FROM tblStates INNER JOIN tblSchools ON tblStates.[StateID] = tblSchools.[StateID]
WHERE (((tblSchools.SchoolName) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolS] & "*" OR (tblSchools.SchoolName is null)) AND 
((tblSchools.SchoolPostCode) Like "*" & [Forms]![frmSchoolSearch]![txtFilterSchoolPostCode] & "*" OR (tblSchools.SchoolPostCode is null)) AND 
((tblSchools.SchoolSuburb) Like "*" & [Forms]![frmSchoolSearch]![txtSchoolSub] & "*" OR (tblSchools.SchoolSuburb is null)) AND 
((tblSchools.SchoolPhone) Like "*" & [Forms]![frmSchoolSearch]![txtPhone] & "*" OR (tblSchools.SchoolPhone is null)) AND 
((tblSchools.SchoolEmail) Like "*" & [Forms]![frmSchoolSearch]![txtEmail] & "*" OR (tblSchools.SchoolEmail is null)) AND 
((tblSchools.[1ContactSurname]) Like "*" & [Forms]![frmSchoolSearch]![txtSurname] & "*" OR (tblSchools.[1ContactSurname] is null)) AND 
((tblSchools.StateID)=Nz([Forms]![frmSchoolSearch]![cmbState],[tblSchools]![StateID]))) 
ORDER BY tblSchools.NewSchoolsID;

Is this a saved query or is it in the record source of the form?

I can think or 2 or 3 methods that might work for your situation depending on your design. Would you explain how you are searching? Forms, buttons text boxes or combo boxes,....

It's not a saved query, it's the record source from the form.

You search any of the text boxes on the main form. It's after update (press return) and lost focus (tab to next field). It works great but of course any with nulls like say "txtEmail" won't show. The results show in a continuous subform.

[Ruegen]

Have you tested the query without the "StateID" criteria ?If the [Forms]![frmSchoolSearch]![cmbState] is not null, are the results correct ?

Yes since the school has to be allocated for a state there will never be a null. The results are correct. It's a drop down combo box for that particular one (hence cmbState) and the results are correct (however having it list nulls just in case the school hasn't been allocated to state would be nice).

There are a LOT of fields that I could potentially use that may or may not have nulls. For the ones that will never have nulls like state or school type there won't be nulls however email or surname will. Plus if someone forgets to allocated information to a school it would be nice if that still showed up if it happened to be a null.

[ssanfu]

One of the methods I use:

Set the search form record source to

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolPostCode, tblSchools.SchoolSuburb, tblSchools.SchoolPhone, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.StateID, tblStates.SchoolState
FROM tblStates INNER JOIN tblSchools ON tblStates.[StateID] = tblSchools.[StateID]
ORDER BY tblSchools.NewSchoolsID;

(No WHERE clause)



I would have all of the search controls as combo boxes - it is easier that having to type in data - especially the school name.
In the click event of the button, I would check each control to see if had an entry, then build up the filter string. Set the form filter to the string generated in the code and turn on the filter.

You could also have a clear filter button to show all schools and clear the search controls.

[Ruegen]

One of the methods I use:

Set the search form record source to

SELECT tblSchools.NewSchoolsID, tblSchools.SchoolName, tblSchools.SchoolAddress, tblSchools.SchoolPostCode, tblSchools.SchoolSuburb, tblSchools.SchoolPhone, tblSchools.SchoolEmail, tblSchools.[1ContactName], tblSchools.[1ContactSurname], tblSchools.StateID, tblStates.SchoolState
FROM tblStates INNER JOIN tblSchools ON tblStates.[StateID] = tblSchools.[StateID]
ORDER BY tblSchools.NewSchoolsID;

(No WHERE clause)



I would have all of the search controls as combo boxes - it is easier that having to type in data - especially the school name.
In the click event of the button, I would check each control to see if had an entry, then build up the filter string. Set the form filter to the string generated in the code and turn on the filter.

You could also have a clear filter button to show all schools and clear the search controls.

ok so that would mean it would only search for records after clicking a button? I was hoping it would search after using each textbox/combo box sort of what I have now.

also all that (that I have on the image in the previous post) button does is put all the fields back to null and requery the list...

[Ruegen]

Would this work? Place it on the after update of the txtbox and have it filter the subform query?

http://msdn.microsoft.com/en-us/libr.../ff197651.aspx

[ssanfu]

Would this work? Place it on the after update of the txtbox and have it filter the subform query?

The command "DoCmd.ApplyFilter , "LastName = 'King'" " might work. I have never used it. You would need to have code to test each control...

To set a filter, I use:

Dim sFilterString As String
'
' VBA code to create the filter string
'  
sFilterString = "Country = 'USA'"

Me.Filter = sFilterString 
Me.FilterOn = True

To clear a filter, I use:

Me.Filter = ""
Me.FilterOn = False