combo box filter continuous form with cmd button but no null values showing

H

heidii

Hello:

I am very new to writing code and the code I am using I have gathered
from these newsgroups and the web.

I have the following code in placed under the OnClick even of my
command button on a continuous form:

Private Sub CMDSEARCH_Click()
Me.Filter = "([YEAR] Like '*" & cboYEAR & "')And " & _
"([MAKE] Like '*" & cboMAKE & "') And " & _
"([Model] Like '*" & cboMODEL & "') And " & _
"([COLOR] Like '*" & cboCOLOR & "') And " & _
"([Description] Like '*" & cboDESCRIPTION & "') And " & _
"([License] Like '*" & cboLIC & "') And " & _
"([Location] Like '*" & cboLOCATION & "') And " & _
"([Driver Name] Like '*" & cboDRIVER & "') And " & _
"([TYPE] Like '*" & cboTYPE & "') And " & _
"([STATUS] Like '*" & cboSTATUS & "') And " & _
"([VIN] Like '*" & cboVIN & "') "
Me.FilterOn = True
End Sub

and I have the following code under another command button on the same
form to refresh the search combo boxes:

Private Sub CMDREFRESH_Click()
Me.FilterOn = True
Me.cboMAKE = ""
Me.cboYEAR = ""
Me.cboCOLOR = ""
Me.cboVIN = ""
Me.cboSTATUS = ""
Me.cboTYPE = ""
Me.cboDESCRIPTION = ""
Me.cboDRIVER = ""
Me.cboLIC = ""
Me.cboLOCATION = ""
Me.cboMODEL = ""
DoCmd.ShowAllRecords
Requery
End Sub

Both of these work great, except after I enter my search criteria in
the combo boxes and hit my command button, my return record results do
not include records where one of the columns has a null value.

Example:

I put in 2002 for the Year Combo box, and then click my Search command
button. Only the 2002 vehicles that have all the fields filled in
return. But there are records that have 2002 for the vehicle year
with no color entered. How do I get those records to show up.

Thanks Heidi
 
S

Svetlana

You can just remove the filter.

Private Sub CMDREFRESH_Click()
Me.FilterOn = False
Me.cboMAKE = Null
Me.cboYEAR = Null
Me.cboCOLOR = Null
Me.cboVIN = Null
Me.cboSTATUS = Null
Me.cboTYPE = Null
Me.cboDESCRIPTION = Null
Me.cboDRIVER = Null
Me.cboLIC = Null
Me.cboLOCATION = Null
Me.cboMODEL = Null
End Sub
 
H

heidii

You can just remove the filter.

Private Sub CMDREFRESH_Click()
Me.FilterOn = False
Me.cboMAKE = Null
Me.cboYEAR = Null
Me.cboCOLOR = Null
Me.cboVIN = Null
Me.cboSTATUS = Null
Me.cboTYPE = Null
Me.cboDESCRIPTION = Null
Me.cboDRIVER = Null
Me.cboLIC = Null
Me.cboLOCATION = Null
Me.cboMODEL = Null
End Sub



That's not exactly what I meant. When somebody is using the filter
as a search, some of the records may not have all the colums filled in
on that particular record. Those records are not showing when the
filter runs.

Example: only records that have all the columns filled in return as a
result

Vehicle Year Make Model Color Vin LIC
2002 Ford F150 Red asdf asdfa

I need all records to return based on any of the filter values:
Vehicle Year Make Model Color Vin LIC
2002 Ford F150 NULL asdf asdfa

Making the them all null on the Refresh button is not going to work.
As that is what the Refresh button does to begin with, makes all combo
boxes empty.

Needs a solution on the filter itself to include Null values.
 
S

Svetlana

You need to create a more complicate filter so you can deal with all
possible values.
Here is an examble:

Dim blnYear As Boolean
blnYear=IsNull(Me.cboYear)

Dim blnMake As Boolean
blnMake=IsNull(Me.cboMake)

Dim blnModel As Boolean
blnModel=IsNull(Me.cboModel)

Dim strYear As String

If blnYear=False Then
strYear = " [Year]=" & """" & Me.cboYear & """"
Else
strYear = ""
End If

Dim strMake As String

If blnMake=False Then
strMake = " [Make]=" & """" & Me.cboMake & """"
Else
strMake = ""
End If

Dim strModel As String

If blnModel=False Then
strModel = " [Model]=" & """" & Me.cboModel & """"
Else
strModel = ""
End If

Dim strFilter As String
strFilter=""

If strYear<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strYear
Else
strFilter=strFilter
End If

If strMake<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strMake
Else
strFilter=strFilter
End If

If strModel<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strModel
Else
strFilter=strFilter
End If

Dim lngLenghOfFilter As Long
lngLenghOfFilter=Len(strFilter)

strFilter=Mid(strFilter, 5, lngLenghOfFilter)

Msgbox strFilter 'you can use it for check

Me.Filter=strFilter
Me.FilterOn=True
 
H

heidii

You need to create a more complicate filter so you can deal with all
possible values.
Here is an examble:

Dim blnYear As Boolean
blnYear=IsNull(Me.cboYear)

Dim blnMake As Boolean
blnMake=IsNull(Me.cboMake)

Dim blnModel As Boolean
blnModel=IsNull(Me.cboModel)

Dim strYear As String

If blnYear=False Then
strYear = " [Year]=" & """" & Me.cboYear & """"
Else
strYear = ""
End If

Dim strMake As String

If blnMake=False Then
strMake = " [Make]=" & """" & Me.cboMake & """"
Else
strMake = ""
End If

Dim strModel As String

If blnModel=False Then
strModel = " [Model]=" & """" & Me.cboModel & """"
Else
strModel = ""
End If

Dim strFilter As String
strFilter=""

If strYear<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strYear
Else
strFilter=strFilter
End If

If strMake<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strMake
Else
strFilter=strFilter
End If

If strModel<>"" Then
strFilter=strFilter & Chr(32) & "AND" & Chr(32) & strModel
Else
strFilter=strFilter
End If

Dim lngLenghOfFilter As Long
lngLenghOfFilter=Len(strFilter)

strFilter=Mid(strFilter, 5, lngLenghOfFilter)

Msgbox strFilter 'you can use it for check

Me.Filter=strFilter
Me.FilterOn=True

I like how the msgbox strFilter shows what is being filtered. It has
allowed me to see that part of it is not working after the first
try.

When I initially open the form, I can run a search, and the filter
works. After I clear the filter with my refresh code, my entries
become null.

Then I try another search and get nothing. when the message box comes
up to show the filter you are using it is including more than it
should.

What went wrong?
 
H

heidii

Sorry for the double post, but I solved the clear filter issue with
other code from the previous person. It clears the filter
correctly.

Now I have the issue of how can I include LIKE in the filter code you
gave me. So that if someone only knows the last 5 of the VIN they can
search by just that and get all records that match.

Thanks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top