Search Form with Blank Feilds

[nrypka]

Hi, I have very little experience with Access and need some help with a search field I am creating.
I attached the search bar I created. The problem I am having is that four of the fields there is no data for, so when I include all of the fields in my search it returns nothing. I began using a macro and a where condition to ApplyFilter, then I ran into the problem that there can only be 255 characters, so I then converted it to Visual Basic using this code.

DoCmd.ApplyFilter "", "[Description] Like ""*"" & [Forms]![Search1]![Text46] & ""*"" And [Make] Like ""*"" & [Forms]![Search1]![Text114] & ""*"" And [Model] Like ""*"" & [Forms]![Search1]![Text116] & ""*"" And [Serial] Like ""*"" & [Forms]![Search1]![Text118] & ""*"" And [Status] Like ""*"" & [Forms]![Search1]![Text120] & ""*"" And [Department] Like ""*"" & [Forms]![Search1]![Combo125] & ""*"" And [ID] Like ""*"" & [Forms]![Search1]![Text55] & ""*"" And [NextCalDate] Like ""*"" & [Forms]![Search1]![Text60] & ""*""", ""



When I only have the four fields with all of the data this code works fine, but if there are any blanks in any field it does not return that data. Any help would be great, and I will provide more information if necessary. Thank you

[John_G]

When I only have the four fields with all of the data this code works fine,...

What four fields? Your form shows eight, and your code references all eight without distinguishing any particular group of four.

In a case like this you would have to use VBA to assemble your filter into a string, using only search fields you entered a value for, and then use the string in the DoCmd.ApplyFilter statement.

You probably don't need wild card characters for the two dropdown lists, since they are giving you a set of specific values to choose from.

strFilter = ""
if not isnull ([description]) then
  strfilter = "[Description] Like ""*"" & [Forms]![Search1]![Text46] & ""*""
endif
if not isnull([make] then
  if len(strfilter) > 0 then strfilter = strfilter & " AND "   ' Puts AND in only if it needs to
  strfilter = strfilter & [Make] Like ""*"" & [Forms]![Search1]![Text114] & ""*""
endif
'
' And the same format for the others
'

DoCmd.ApplyFilter "", strFilter

...but if there are any blanks in any field it does not return that data.

It's not clear what you mean there - are you referring to blanks in any field on the form, or any field in your record source table/query?

[nrypka]

I'm sorry for the unclear explanation, let me try a little better.
There are eight fields, Make, Model, Serial, Status, Description, Department, ID, and Next Cal Date. Four of these feilds (Description, Department, ID and Next Cal Date) have Data for all entries, whereas the other four Feilds (Make, Model, Serial and Status) have almost no data in any single entry.
What I meant to say was that if I removed only the code that searched for Make, Model, Serial, and Status (the ones with "blanks") and left the code to search the other fields it works perfectly. I am currently working on trying your approach to my problem to see if I can get it to work. Thank you very much John_G

[ssanfu]

DoCmd.ApplyFilter "", "[Description] Like ""*"" & [Forms]![Search1]![Text46] & ""*"" And [Make] Like ""*"" & [Forms]![Search1]![Text114] & ""*"" And [Model] Like ""*"" & [Forms]![Search1]![Text116] & ""*"" And [Serial] Like ""*"" & [Forms]![Search1]![Text118] & ""*"" And [Status] Like ""*"" & [Forms]![Search1]![Text120] & ""*"" And [Department] Like ""*"" & [Forms]![Search1]![Combo125] & ""*"" And [ID] Like ""*"" & [Forms]![Search1]![Text55] & ""*"" And [NextCalDate] Like ""*"" & [Forms]![Search1]![Text60] & ""*""", ""

This is definitely filtering the hard way.

As John_G said, use VBA to create the filter string (like a WHERE clause but without the word "WHERE").
Then use:
Me.Filter = (the filter string)
Me.FilterOn = True

See http://www.allenbrowne.com/ser-62.html
The actually code example is here http://www.allenbrowne.com/ser-62code.html

[nrypka]

I have been working with the code that was given by ssanfu and John_G and have come up with

DoCmd.ApplyFilter "", strfilter = ""
If Not IsNull(Make) Then
        strWhere = strWhere & "([Make] = """ & [Forms]![Search1]![Text114] & """) AND "
End If


If Not IsNull(Description) Then
        strfilter = strWhere & "([Description] = """ & [Forms]![Search1]![Text46] & """) AND "
End If


If Not IsNull(Model) Then
        strWhere = strWhere & "([Model] = """ & [Forms]![Search1]![Text116] & """) AND "
End If


If Not IsNull(Serial) Then
        strWhere = strWhere & "([Serial] = """ & [Forms]![Search1]![Text118] & """) AND "
End If


If Not IsNull(ID) Then
        strWhere = strWhere & "([ID] = " & [Forms]![Search1]![Text55] & ") AND "
End If


If Not IsNull(NextCalDate) Then
        strWhere = strWhere & "([NextCalDate] >= " & [Forms]![Search1]![Text60] & ") AND "
End If


If Not IsNull(NextCalDate) Then   'Less than the next day.
        strWhere = strWhere & "([NextCalDate] < " & [Forms]![Search1]![Text240] & ") AND "
End If


If Not IsNull(Department) Then
        strWhere = strWhere & "([Department] = " & [Forms]![Search1]![Combo125] & ") AND "
End If


If Not IsNull(Status) Then
        strWhere = strWhere & "([Status] = " & [Forms]![Search1]![Combo132] & ")"
End If

This code allows my description field to search, but no other field. It also searches through all of the other fields as well. I'm sure that there is something I am missing, but I am still learning Visual Basic so any help would be great. Thank you

[John_G]

You have to apply the filter AFTER you put the filter string together, and it must reference strWhere:

DoCmd.ApplyFilter "", strWhere

One thing to note is that if you do not include "status" as one of your criteria, you will get an error because there will be an extra "AND" at the end of the character string with nothing following it.

Put the "AND" at the beginning of each line adding to strWhere, but only if you need to. That was the purpose of this line:

if len(strfilter) > 0 then strfilter = strfilter & " AND " ' Puts AND in only if it needs to

I have in post #2.

[nrypka]

I added that code and it still isn't working correctly. The description field is searching through all of the other fields as well as its own, and the other fields do not search at all.

[John_G]

Try using debug.print strWhere to output the contents of the strWhere string to the immediate pane (Crtl-G) then post it here. Indicate what data you entered on the form to generate the resulting string.

That will give us some idea of what the problem might be.

[ssanfu]

Your code looks wonky (technical term )

Here is a snippet of the code

If Not IsNull(Make) Then
        strWhere = strWhere & "([Make] = """ & [Forms]![Search1]![Text114] & """) AND "
End If

This makes no sense. What is "[Forms]![Search1]![Text114]"????

Here is a snippet from Allen Browne's code

If Not IsNull(Me.txtFilterCity) Then
        strWhere = strWhere & "([City] = """ & Me.txtFilterCity & """) AND "
End If

"Me.txtFilterCity" is the text box control on the form that is being checked to see if the control is null or not (or the length is zero).
"[City]" is the field in the recordsource
"([City] = """ & Me.txtFilterCity & """) is creating the comparison between the text box control and the field in the record source.

Your code is not the same (disregarding the difference in field/control names.

To have your code snippet match Allen's snippet (in structure), it should look like:

If Not IsNull([Forms]![Search1]![Text114]) Then
        strWhere = strWhere & "([Make] = """ & [Forms]![Search1]![Text114] & """) AND "
End If

I always take the time to rename controls, so I would have renamed the text box control for the "Make" data you want to search for to "tbMake" (the prefix tb = text box).

Then the code snippet would be

If Not IsNull([Forms]![Search1]![tbMake]) Then
        strWhere = strWhere & "([Make] = """ & [Forms]![Search1]![tbMake] & """) AND "
End If

This could be shortened to

If Not IsNull(Me.tbMake) Then
        strWhere = strWhere & "([Make] = """ & Me.tbMake & """) AND "
End If

It also looks like the last "IF" statement is missing the " AND " text.

What is the complete routine for searching??

[nrypka]

I took your advice ssanfu to change the name of the controls to make life easier, now when I search I am not getting any results. I mostly have used the Allen Browne code as a template but am trying to use John_G's advice to use

if len(strfilter) > 0 then strfilter = strfilter & " AND "

. Using the debugger with a search of "gage" in the description field it shows "([Description] = "gage") AND "

here is what the updated code I have looks like.

Dim strWhere As String                  'The criteria string.
Dim lngLen As Long                      'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.


DoCmd.ApplyFilter "", strfilter = ""
If Not IsNull(Me.tbMake) Then
        strWhere = strWhere & "([Make] = """ & Me.tbMake & """) AND "
End If


If Not IsNull(Me.tbDescription) Then
        If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([Description] = """ & Me.tbDescription & """) AND "
End If


If Not IsNull(Me.tbModel) Then
        If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([Model] = """ & Me.tbModel & """) AND "
End If


If Not IsNull(Me.tbSerial) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([Serial] = """ & Me.tbSerial & """) AND "
End If


If Not IsNull(Me.tbID) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([ID] = " & Me.tbID & ") AND "
End If


If Not IsNull(Me.tbStartDate) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([NextCalDate] >= " & Me.tbStartDate & ") AND "
End If


If Not IsNull(Me.tbEndDate) Then   'Less than the next day.
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([NextCalDate] < " & Me.tbEndDate & ") AND "
End If


If Not IsNull(Me.cbDepartment) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([Department] = " & Me.cbDepartment & ") AND "
End If


If Not IsNull(Me.cbStatus) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([Status] = " & Me.cbStatus & ")"
End If


Debug.Print strWhere




End Sub

[ssanfu]

I wouldn't expect this code to execute - there are two variables being used to build the filter string - should only have 1 variable.

If Not IsNull(Me.tbID) Then
         If Len(strfilter) > 0 Then strfilter = strfilter & " AND "
        strWhere = strWhere & "([ID] = " & Me.tbID & ") AND "
End If

Look at this code: (you had too many " AND " 's )

Sub searchMe()   '<<<--- I used this - your sub names was missing
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.

    Dim strWhere As String                  'The criteria string.
    '    Dim lngLen As Long                      'Length of the criteria string to append to.

    ' The following line not needed/shouldn't be used.
    'DoCmd.ApplyFilter "", strFilter = ""


    If Not IsNull(Me.tbMake) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Make] = """ & Me.tbMake & """)"
    End If

    If Not IsNull(Me.tbDescription) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Description] = """ & Me.tbDescription & """)"
    End If

    If Not IsNull(Me.tbModel) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Model] = """ & Me.tbModel & """)"
    End If

    If Not IsNull(Me.tbSerial) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Serial] = """ & Me.tbSerial & """)"
    End If

    If Not IsNull(Me.tbID) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([ID] = " & Me.tbID & ")"
    End If

    If Not IsNull(Me.tbStartDate) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([NextCalDate] >= " & Format(Me.tbStartDate, conJetDate) & ")"
    End If


    If Not IsNull(Me.tbEndDate) Then   'Less than the next day.
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([NextCalDate] < " & Format(Me.tbEndDate, conJetDate) & ") "
    End If

    If Not IsNull(Me.cbDepartment) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Department] = " & Me.cbDepartment & ")"
    End If


    If Not IsNull(Me.cbStatus) Then
        If Len(strWhere) > 0 Then
            strWhere = strWhere & " AND "
        End If
        strWhere = strWhere & "([Status] = " & Me.cbStatus & ")"
    End If


    '***********************************************************************
    ' use the string as the form's Filter if length strWhere > 0
    '***********************************************************************
    If Len(Trim(strWhere)) <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        Debug.Print strWhere

        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If


End Sub

I added the format command for the dates.
Not sure if the "Status" and "Department" filter will work - depends what the bound field type is for those two combo boxes - number or text.

Single step through the code so you understand what it is doing....

[orange]

A suggestion:

Try commenting out some of the code.
Get 2 conditions working then move on to 3, then 4.

As was previously suggested make use of a DEBUG.PRINT strWhere, and let us know what results.

[nrypka]

I have all fields except for Status and Department working now. I am going to continue to try and work on those. I appreciate all of the help and advise!

[orange]

I think you are missing a quote x2 in each if the cbStatus and cbDepartment contain strings/text

strWhere = strWhere & "([Department] = '" & Me.cbDepartment & "')"


same for status.

You could /should also include

at the beginning of code

On Error Go to Error_Handler

and at the bottom of your code before the End statement
On Error GoTo 0
Exit Function



Error_Handler:
MsgBox "Error " & err.number & " (" & err.Description & ")

[nrypka]

You were correct orange it was as simple as missing the quotes and now I have my search working perfectly. I also made a reset button following the Allen Browne coding. The last thing I wanted to do was to open a report based off of the search parameters and this is what I have but it just brings up all of the data

DoCmd.OpenReport "Search Report", acViewPreview, , strWhere

Thank you again to everyone that has helped. It is greatly appreciated