entering filter criteria for a report

[SeaRuidoso]

I am in the midst of porting an old Lotus DB to Access. I need to be able to allow a user to input an indefinite number of one-character search criteria into a query/filter in order to generate a report.

I want to use Access' syntax for the purpose -- 'Like "B" or "O" or ...' -- but it seems like simply feeding that line to the query as a parameter results in it being treated as a string constant rather than an actual command. Anyone know any ways to get around this?

FYI - I have a mailing list with the usual fields plus one special field which denotes which groups the individual belongs to. "a" would denote alcoholics, "b" denotes baptist. So for the person I just described the special field would contain "ab" or "ba" - the order is irrelevant. Another person could be just "a" or just "b". The query needs to be able to handle the variation.

-Sea

 

[PHV]

In a standard code module paste this code:

Public Function IsInGroup(Target, Pattern) As Boolean
If Nz(Pattern, "*") = "*" Then IsInGroup = True: Exit Function
Dim i As Long
For i = 1 To Len(Pattern)
  If InStr(Target, Mid(Pattern, i, 1)) > 0 Then
    IsInGroup = True: Exit Function
  End If
Next i
End Function

Then the criteria may be something like this:
strCriteria = "IsInGroup([group field],'" & Me![search textbox] & "')=True"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Your real issue is one of storing multiple values in a single field. A more flexible solution would be a related table that stores your primary key and a group code.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[SeaRuidoso]

Yeah, I appreciate what you're saying. The catch is that I did this for a non-profit which has a secretary who is over seventy years old. She's fixed in her ways to say the least and is absolutely paranoid of change, so any fundamental change to her way of doing things would be a multi-week drama for that particular office. She also periodically works with the table directly which ruled out a structural change in the DB. It took us a year to get her moved off of a DOS 5 box. Anyway, enough picking on her.

I'm pasting the core code that I came up with below. It parses a text box contents (the group codes) and generates a SQL filter for a pre-defined query and the report that sources that query. It's a kludge of sorts, but it works pretty well. The old DB program required the secretary to use a SQL like syntax and enter the statement each time, so this is pretty marvelous to her.

    txtCriteriaInputSort1.SetFocus
    For i = 1 To Len(txtCriteriaInputSort1.Text)
        If sortCde1 = "" Then
            sortCde1 = "[sortcde1] = '" & Mid(txtCriteriaInputSort1.Text, i, 1) & "'"
        Else
            sortCde1 = sortCde1 & " Or [sortcde1] = '" & Mid(txtCriteriaInputSort1.Text, i, 1) & "'"
        End If
    Next i

    DoCmd.OpenReport "Mailing Labels", acViewPreview, "regmail mail label source", sortCde1

-Sea