Query Fields Value and Null

[orbojeff]

I want to create a form that searches from a Table which contains many
Fields with Null Values

I've created a form and the 7 Fields want to search from are Unbound to
a control Source
To this I've added a subform based on a Query where the criteria are
the values from the Unbound Fields

This works well when the Fields have a value
However when the Field has no Value it ignores the Record.

I tried building the expression:
[frm_SEARCH]![cmb_CITY] Or Is Null (where cmb_City is the Unbound Combo
Box on my Form)

However because I have several many Fields this making the Query very
complex.
It appears to need to satisfy every condition and creating a statement
for each

Is there a better way to accomplish this?
Is there a simple Table and of Field setting I'm missing to allow for
this?

 

[Allen Browne]

Yes, there is a better way.

Instead of creating a monster WHERE clause that tries to cope with all
possible cominbinations of the search criteria and nulls in the unbound text
boxes on your form, you can create a Filter on the form based on the
non-blank boxes.

The example below is structured so it can easily be extended to any number
of controls. It builds up the string based on any non-null search boxes,
tacking " AND " onto the end of each one, and then cuts the trailing " AND "
at the end. Text based fields need quotes as delimiters, and date fields
need delimiting and formatting to work in all regional settings, so the 3
examples below illustrate how to handle each field type.


Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then 'Save any edits first.
Me.Dirty = False
End If

If Not IsNull(Me.cmb_City) Then 'Text field example.
strWhere = strWhere & "([City] = """ & Me.cmb_City & """) AND "
End If

If Not IsNull(Me.txtAmount) Then 'Number field example.
strWhere = strWhere & "([Amount] = " & Me.txtAmount & ") AND "
End If

If Not IsNull(Me.txtInvoiceDate) Then 'Date field example.
strWhere = strWhere & "([InvoiceDate] = " &
Format(Me.txtInvoiceDate, conJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

 

[orbojeff]

Allen

Thanks for the help, I'm just having a little trouble implementing it

Should I structure my Query the same as before, just without the Null
Clauses?
Is there an example you can point me towrds?

Thanks
Jeff

 

[Allen Browne]

You can leave out of the query all references to
[Forms].[MyForm].[MyTextbox]

Then when you load the form, it shows all the records. And when you enter
some values into the unbound text boxes and click your Filter command
button, it limits the form to matching values.

 

[orbojeff]

OK, that makes sense.
Now I just need some pointers on the Code.
All of my Search Fields are Text

cmb_ROUTING
txt_PAYOR_SCORE
txt_AMOUNT
cmb_DOCUMENT_TYPE
cmb_CITY
cmb_STATE
cmb_ZIP

My query is qry_SEARCH in the SubForm sfrm_SEARCH

Also I receive the error "You entered an expression that has an invalid
reference to the property Dirty

THANKS
Jeff

 

[Allen Browne]

I am assuming that you have a form bound to the query, so that it shows the
result of the query. If the form is unbound (there is Nothing in its
RecordSource property), then it will not have a Dirty property, and the
approach of trying to apply a Filter won't work.

After setting the RecordSource of the form to the query, you will have bound
controls (to show the results of the search) in the Detail section of your
form , and unbound controls (for entering the search values) in the Form
Header section of your form. You will therefore need to adjust the code to
search the field names based on the values in the unbound controls.

 

[orbojeff]

Allen

Sorry to keep bother you, I'm somewhat of a novice

I've did what you said...
I've bound the Main Form frm_SEARCH o my Query, qry_SEARCH
The controls in the Header are unbound and have the following names:
cmb_ROUTING
txt_PAYOR_SCORE
txt_AMOUNT
cmb_DOCUMENT_TYPE
cmb_CITY
cmb_STATE
cmb_ZIP

I created a subform based on the Query (did I need to make a subform)
I'm using the following Code

Private Sub cmd_SEARCH_Click()

Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then 'Save any edits first.
Me.Dirty = False
End If

If Not IsNull(Me.cmb_CITY) Then 'Text field example.
strWhere = strWhere & "([CITY] = """ & Me.cmb_CITY & """) AND"
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If

End Sub

When I type in the city "BURLINGTON", it does limit it to Burlington,
but there are also unrelated records
Any kind of example you can direct me to would be very much appreciated

Thanks
Jeff

 

[Allen Browne]

No need for a subform. You apply the filter to the main form.

Typically you set the form's DefaultView property to Continuous Form, and
arrange all the controls one-beside-the-other so you can see the search
results one-per-line.

For an example, open the Northwind sample database, and look at the Customer
Phone List form. It has that layout. (It is actually much more limited in
that it only filters on one character of one field, but the principle of the
search results and applying a Filter to the form is the same.)