Solved Filter data in a form based on a text value

[zebrafoot]

Hello,

I have a simple database to record jobs we are carrying out. My employer has asked me to add a text filter to enable a quick search through the jobs listed on a given form.

Normally, when going to a form, would add a criterion to the underlying query in order to filter the data. However, I don't seem to be able to filter using a text string in the form that is referencing the query.

I've successfully filtered data using this sort of code:
DoCmd.SetFilter wherecondition:="[JobTypeID] = 6"

This works for me where I'm using a discrete value like the number 6, above. However, I don't understand how to specify the filter if I want to search for text.

Can anyone help?

Best wishes,
Pete

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything
Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here the output from the debug.print for someone else to spot it.

Example:

tt="Eg'g"

? dlookup("FoodID","tblFoods","FoodName = """ & tt & """")

 

[arnelgp]

also try:

DoCmd.SetFilter wherecondition:="[JobTypeID] = " & [yourTextboxName]

 

[zebrafoot]

Thank you for taking the time to answer. How do I filter so that I return data that contains (but doesn't equal) a string?

 

[Ken Sheridan]

How do I filter so that I return data that contains (but doesn't equal) a string?.

If you wish to filter a form or report on the basis of a substring within a value in a column of text data type, you can use the Like operator and wildcard characters, e.g.

"LastName Like ""*"" & txtSearch & """*"""

However, this can result in specious substring matches, e.g. searching on the basis of the substring "william" would return rows with the values "Williams", "Williamson", "Fitzwilliam" etc. To return rows on the basis of words or phrases rather than substrings the following function can be used:

Public Function FindWord(varFindIn As Variant, varWord As Variant) As Boolean

   Const PUNCLIST = """' .,?!:;(){}[]-—/"
   Dim intPos As Integer
   
   FindWord = False
   
   If Not IsNull(varFindIn) And Not IsNull(varWord) Then
       intPos = InStr(varFindIn, varWord)
       
       ' loop until no instances of sought substring found
       Do While intPos > 0
           ' is it at start of string
           If intPos = 1 Then
               ' is it whole string?
               If Len(varFindIn) = Len(varWord) Then
                   FindWord = True
                   Exit Function
               ' is it followed by a space or punctuation mark?
               ElseIf InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
                   FindWord = True
                   Exit Function
               End If
           Else
               ' is it precedeed by a space or punctuation mark?
               If InStr(PUNCLIST, Mid(varFindIn, intPos - 1, 1)) > 0 Then
                   ' is it at end of string or followed by a space or punctuation mark?
                   If InStr(PUNCLIST, Mid(varFindIn, intPos + Len(varWord), 1)) > 0 Then
                       FindWord = True
                       Exit Function
                   End If
               End If
           End If
           
           ' remove characters up to end of first instance
           ' of sought substring before looping
           varFindIn = Mid(varFindIn, intPos + 1)
           intPos = InStr(varFindIn, varWord)
       Loop
   End If

End Function

I've attached a little demo file which illustrates the use of the function.

 

[arnelgp]

for Not Equal:

DoCmd.SetFilter wherecondition:="[JobTypeID] <> " & [yourTextboxName]

 

[zebrafoot]

This isn't working for me. I think there's an error with the syntax, which I still don't understand at all!

dim strFilter as string

strFilter = Me.txtFilter


DoCmd.SetFilter wherecondition:="[JobName] like ""*"" & strFilter & """ * """"

Give me the error "13 type mismatch".

 

[arnelgp]

strFilter = Me.txtFilter
DoCmd.SetFilter wherecondition:="[JobName] like '*" & strFilter & "*'"

 

[Ken Sheridan]

DoCmd.SetFilter wherecondition:="[JobName] like ""*"" & strFilter & """ * """"

Mea Culpa! It should have been:

"[JobName] like ""*" & strFilter & "*"""

Unlike the use of the single quotes character, this allows for a string with an apostrophe in them, particularly important with personal names, such as my own name in its original non-anglicized form, Cináed O'Siridean.

 

[zebrafoot]

Thanks to all for your contribution. It's working now!

For future reference, is it possible to reference a control in the form as a criterion in the underlying query, or is this not possible in Access?

 

[Ken Sheridan]

For future reference, is it possible to reference a control in the form as a criterion in the underlying query, or is this not possible in Access?

Yes. In the query make a parameter a reference to the form using the following syntax:

[Forms]![NameOfFormGoesHere]![NameOf ControlGoesHere]

You don't need to worry about delimiting the value, but I'd advise that date parameters be declared as DATETIME to avoid their being inadvertently misinterpreted as arithmetic expressions:

PARAMETERS [Forms]![NameOfFormGoesHere]![NameOfControlGoesHere] DATETIME;
SELECT *
FROM [NameOfTableGoesHere]
WHERE [NameofDateField] = [Forms]![NameOfFormGoesHere]![NameOfControlGoesHere];

PS: You can make a parameter optional like this:

PARAMETERS [Forms]![NameOfFormGoesHere]![NameOfControlGoesHere] DATETIME;
SELECT *
FROM [NameOfTableGoesHere]
WHERE ([NameofDateField] = [Forms]![NameOfFormGoesHere]![NameOf ControlGoesHere]
   OR [Forms]![NameOfFormGoesHere]![NameOfControlGoesHere] IS NULL);

 

[zebrafoot]

Thank you!

 

[zebrafoot]

Further to this,

DoCmd.SetFilter wherecondition:="[JobName] like '*" & strFilter & "*'"

works to filter by the name of the job (contains a string strFilter), imagine I want to add another filter JobTypeID (an integer, defined as intType), how would I write that? To clarify, filter the dataset by job name string AND job type.

 

[Gasman]

You concatenate " AND JobTYpe = " & Me.Jobtype to your criteria

 

[zebrafoot]

Thanks, Gasman. That works:

DoCmd.SetFilter wherecondition:="[JobName] like '*" & strFilter & "*' AND [JobTypeID] = " & intType

I still get confused by the syntax of concatenating strings in Access. The code above seems to work though!

 

[Gasman]

Put it all into string variable, debug.print it until you get it right, then use the string variable in the code/function.

 

[zebrafoot]

I've never used debug.print. I'll have to look into what that is.

 

[Gasman]

I've never used debug.print. I'll have to look into what that is.

It shows the content of what you ask

Dim strCriteria as String

strCriteria = "[JobName] like '*" & strFilter & "*' AND [JobTypeID] = " & intType
Debug.Print strCriteria
DoCmd.SetFilter wherecondition:= strCriteria

The Debug.Print will show what you have, Then you can see any mistakes.

Best part, if you cannot see them, you can copy the output and post back here for help. Then we see EXACTLY what you have.

 

[zebrafoot]

That's really helpful. Thank you again, Gasman.