Multiple field search in Form (1 Viewer)

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
Hello,

I have been creating Access database for some time now and now I came across the issue I cannot find the answer for.

I am trying to create a multifield search on my Form. I did it before with just using Macros, but it has limited me to only 3 fields, now I need to add more.

My code so far looks like this:

Private Sub cmdFilter_Click()
On Error GoTo cmdFilter_Click_Err

DoCmd.ApplyFilter "", "[DB type] Like ""*"" & [Forms]![SearchResult]![FilterDBtype] & ""*""" _
And "[GaugeNum] Like "" * "" & [Forms]![SearchResult]![FilterGaugeNum] & "" * """ _
And [GaugeSerialNum] Like "" * "" & [Forms]![SearchResult]![FilterSerialNum] & "" * """, """


DoCmd.SetOrderBy "GaugeNum", ""


cmdFilter_Click_Exit:
Exit Sub

cmdFilter_Click_Err:
MsgBox Error$
Resume cmdFilter_Click_Exit

End Sub


And when click Search button it gives me an error "Type Mismatch" and I am stuck. Any help, suggestions would be welcome.
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:13
Joined
Sep 21, 2011
Messages
14,052
My first suggestion always for something like this, is put the criteria into a string, then Debug.Print the string.
Normally text needs to be enclosed with single quote ' unless that will occur in the text, then I *think* you must use triple double quotes "
Dates need # and numerics nothing at all.

Your " look wrong to me? Alternatively use the CHR() equivalents

Post back what the Debug.Print displays by a copy and paste.
Please put that within code tags (</> icon above), so as to make it easier to read and spot any errors.

This link might also help.

 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2013
Messages
16,555
all those double quotes - and looks like the code wouldn't run anyway

"[DB type] Like '*" & FilterDBtype & "*' AND" & _
" [GaugeNum] Like '*" & FilterGaugeNum & "*' AND" & _
" [GaugeSerialNum] Like '*" & FilterSerialNum & "*'"

this assumes all three fields are text (using 'num' in your field names implies they are actually numeric
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
If I run it like that (see below) it works. As soon as I add GaugeNum it stops and gives me Type Mismatch error.
And yes, GaugeNum is numeric.

<Private Sub cmdFilter_Click()
On Error GoTo cmdFilter_Click_Err

DoCmd.ApplyFilter "", "[DB type] Like ""*"" & [Forms]![SearchResult]![FilterDBtype] & ""*""" _



DoCmd.SetOrderBy "GaugeNum", ""
Debug.Print

cmdFilter_Click_Exit:
Exit Sub

cmdFilter_Click_Err:
MsgBox Error$
Resume cmdFilter_Click_Exit

End Sub/>
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2013
Messages
16,555
you have a _ at the end which should not be there
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
I figured that out after I posted.

I still can't get it run with more then one criteria.

Also is it possible to search for letters and numbers at the same time?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:13
Joined
Feb 19, 2013
Messages
16,555
look at the example I provided

Also is it possible to search for letters and numbers at the same time?
of course -but provide some examples of what you mean
 

missinglinq

AWF VIP
Local time
Today, 09:13
Joined
Jun 20, 2003
Messages
6,423
First off...I'm pretty sure you can't use the Like operator with a Numeric datatype...such as GaugeNum.

And despite DoCmd.SetOrderBy being used in Microsoft help sites...
Typing in DoCmd.setorderby, and moving off of the line, it doesn't become DoCmd.SetOrderBy, as it should. Nor will SetOrderBy come up, from Intellisense, when entering DoCmd.s...as it should.

Linq ;0)>
 

Cronk

Registered User.
Local time
Tomorrow, 00:13
Joined
Jul 4, 2013
Messages
2,770
A work around using Like with numeric values is
cstr([NumberField]) like "n*"
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
look at the example I provided

of course -but provide some examples of what you mean


With DB type I have no issues, as this consists only from letters, GaugeNum is 9 digit number and GaugeSerialNum can be numbers and letters.

The idea is to allow user to type in information into relevant boxes and search for records by typing info fully, partially or leaving some of the fields blank. I have tried different ways, but cannot figure out how to do it.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
you add some Variables to your search routine:

Code:
Dim strDBType As String
Dim strGauge As String
Dim strGaugeSN As String
Dim strFilter As String

If Len([FilterDBType & "")>0 Then
    strDBType="[DB Type] Like '*" & [FilterDBType] & "*' And "
End If
If Len([FilterGaugeNum] & "") > 0 Then
    strGauge = "InStr([Gaugenum] & '', " & [FilterGaugeNum] & ") > 0 And "
End If
If Len([FilterSerialNum] & "") > 0 Then
    strGaugeSN = "[GaugeSerialNum] Like '*" & [FilterSerialNum] & "*'"
End If

strFilter = strDBType & strGauge & strGaugeSN
If Len(strFilter) > 0 Then
    If Right(strFilter, 4) = "And " Then
        strFilter = Trim(Left(strFilter, Len(strFilter)-4))
    End If
End If

DoCmd.ApplyFilter strFilter
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
you add some Variables to your search routine:

Code:
Dim strDBType As String
Dim strGauge As String
Dim strGaugeSN As String
Dim strFilter As String

If Len([FilterDBType & "")>0 Then
    strDBType="[DB Type] Like '*" & [FilterDBType] & "*' And "
End If
If Len([FilterGaugeNum] & "") > 0 Then
    strGauge = "InStr([Gaugenum] & '', " & [FilterGaugeNum] & ") > 0 And "
End If
If Len([FilterSerialNum] & "") > 0 Then
    strGaugeSN = "[GaugeSerialNum] Like '*" & [FilterSerialNum] & "*'"
End If

strFilter = strDBType & strGauge & strGaugeSN
If Len(strFilter) > 0 Then
    If Right(strFilter, 4) = "And " Then
        strFilter = Trim(Left(strFilter, Len(strFilter)-4))
    End If
End If

DoCmd.ApplyFilter strFilter

Tank you for your help, but it is only returning results for DBtype, nothing esle works for some reason.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:13
Joined
May 7, 2009
Messages
19,175
you can upload a sample db.
only you can see your db and what you have tried.
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
you can upload a sample db.
only you can see your db and what you have tried.

Spent a day trying to figure it all out, now it is returning results only when I type something in DB type.
For example if I search for DB type = Internal, it returns the list with only Internal gauges, when I search for DB Type = Internal and Gauge Num = 03000, it returns the list with both these criterias, but if I leave DB type blank, it gives me a runtime error, see attached and highlights

DoCmd.ApplyFilter strFilter
 

Attachments

  • Capture.JPG
    Capture.JPG
    19.3 KB · Views: 112

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
First off...I'm pretty sure you can't use the Like operator with a Numeric datatype...such as GaugeNum.

Like operator works fine with numbers and even date fields. The values are implicitly cast to strings.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
You are closing the double quotes right before the continuation so the And is outside the string and being processed in VBA.

Try this:
Code:
DoCmd.ApplyFilter , "[DB type] Like ""*"" & [Forms]![SearchResult]![FilterDBtype] & ""*"" _
 And [GaugeNum] Like ""*"" & [Forms]![SearchResult]![FilterGaugeNum] & ""*"" _
 And [GaugeSerialNum] Like ""*"" & [Forms]![SearchResult]![FilterSerialNum] & ""*"""

The first double quote opens the string and the last one closes it. All those in between are self escaped by doubling.

According to the documentation, the first argument (filtername) should be blank not a NullString but maybe it doesn't matter.
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
BTW I noticed some really weird things going in with spaces being added or removed when I copied and pasted and then when I edited my post so watch out that you get the same as appears in my post when you paste it into your code.

BTW I have never use DoCmd.ApplyFilter. It still doesn't look quite right to me but I based what I posted on what you said worked.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
Using all double quotes I would expect this:
Code:
DoCmd.ApplyFilter , "[DB type] Like ""*" & [Forms]![SearchResult]![FilterDBtype] & "*"" _
 And [GaugeNum] Like ""*" & [Forms]![SearchResult]![FilterGaugeNum] & "*"" _
 And [GaugeSerialNum] Like ""*" & [Forms]![SearchResult]![FilterSerialNum] & "*"""

or with Single quotes, much easier to read but will cause trouble if the controls contain any single quotes:
Code:
DoCmd.ApplyFilter , "[DB type] Like '*" & [Forms]![SearchResult]![FilterDBtype] & "*' _
 And [GaugeNum] Like '*" & [Forms]![SearchResult]![FilterGaugeNum] & "*'" _
 And [GaugeSerialNum] Like '*" & [Forms]![SearchResult]![FilterSerialNum] & "*'"

If there is a chance of them appearing you can escape any single or double quotes coming from the controls using REPLACE() to double them.
Alternatively if the data never has them use REPLACE() to drop them.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:13
Joined
Jan 20, 2009
Messages
12,849
🤔 I see the difference and what is getting everyone (including me) confused.

In the first version I posted, the concatenation is inside the filter string. The form references are interpreted inside the string. In this technique the string delimiters are not required because Access handles the datatypes itself. (You nearly had it right.)

For the two versions in the later post, the concatenation is in VBA with the form references read by VBA and concatenated into the string. This way the delimiters must be added as appropriate. This is the technique that most of us were expecting.

Either technique should work here.

BTW If you come to understanding the difference, it will teach you much about concatenation and passing values into strings as you develop your VBA skills.

The second technique is really important when generating SQL commands for CurrentDb.Execute because Execute is a Method of the database and the resulting command string is passed directly to the engine. Form references are outside of the database scope so the values must be concatenated into the string.

The first technique works with DoCmd.RunSQL because it is a Method of the Application and Access can interpret the form references.
 

meiteniite89

New member
Local time
Today, 13:13
Joined
Aug 13, 2020
Messages
23
Thank you to everyone's help. I have finally got it all sorted and it is working :)
My final code looks like this
Code:
Private Sub cmdFilterSearch_Click()

    Dim strFilter As String
    Dim strDBType As String
    Dim strGaugeN As String
    Dim strGaugeSN As String
    Dim strGaugeT As String
    Dim strGaugeD As String
    Dim strTValue As String
    Dim strArea As String
    Dim strLoc As String
    Dim strLocC As String
    Dim strCostC As String
    Dim strStatus As String
           

    If Len([FilterDBtype] & "") > 0 Then
        strDBType = "[DB Type] Like '*" & [FilterDBtype] & "*' And "
    End If

    If Len([FilterGaugeNum] & "") > 0 Then
        strGaugeN = "InStr([GaugeNum] & '', " & [FilterGaugeNum] & ") > 0 And "
    End If
   
    If Len([FilterSerialNum] & "") > 0 Then
        strGaugeSN = "[GaugeSerialNum] Like '*" & [FilterSerialNum] & "*' And "
    End If
   
    If Len([FilterGaugeType] & "") > 0 Then
        strGaugeT = "[GaugeType] Like '*" & [FilterGaugeType] & "*' And "
    End If
   
    If Len([FilterGaugeDescr] & "") > 0 Then
        strGaugeD = "[GaugeDescr] Like '*" & [FilterGaugeDescr] & "*' And "
    End If
   
    If Len([FilterTValue] & "") > 0 Then
        strTValue = "[TWtorque] Like '*" & [FilterTValue] & "*' And "
    End If
   
    If Len([FilterArea] & "") > 0 Then
        strArea = "[Area] Like '*" & [FilterArea] & "*' And "
    End If
   
    If Len([FilterLocation] & "") > 0 Then
        strLoc = "[Loc] Like '*" & [FilterLocation] & "*' And "
    End If
   
    If Len([FilterLocCode] & "") > 0 Then
        strLocC = "[LocCode] Like '*" & [FilterLocCode] & "*' And "
    End If
   
    If Len([FilterCostCenter] & "") > 0 Then
        strCostC = "[Cost Center] Like '*" & [FilterCostCenter] & "*' And "
    End If
   
    If Len([FilterStatus] & "") > 0 Then
        strStatus = "[Status] Like '*" & [FilterStatus] & "*' "
    End If
   

    strFilter = strDBType & strGaugeN & strGaugeSN & strGaugeT & strGaugeD & strTValue & strArea & strLoc _
                & strLocC & strCostC & strStatus
    If Len(strFilter) > 0 Then
        If Right(strFilter, 4) = "And " Then
            strFilter = Trim(Left(strFilter, Len(strFilter) - 4))
        End If
    End If
 
   
    Me.Filter = strFilter
    Me.FilterOn = True

    DoCmd.SetOrderBy "GaugeNum ASC"

End Sub
 

Users who are viewing this thread

Top Bottom