Criteria - parameter enter more than one choice

[Zonie32]

Hello all. I have a query with a parameter criteria set as follows:

Like [Enter Sponsor or Hit OK for All] & "*"

This works fine. But there are 5 choices of sponsors. So if I want more than one how do I write the expression? Do I need an OR statement somewhere in here?

 

[DrSimon]

I won't look very pretty for the user but you could do this:
Like [Enter 1st Sponsor or Hit OK for All] & "*"
Like [2nd Sponsor]
Like [3rd Sponsor]
etc.
putting them in lines beneath each other as criteria.
Simon Rouse

 

[Zonie32]

You are right DrSimon. It isn't pretty but it does work. I wonder if there is a way to hit OK for all when the first parameter pops up rather than having to hit ok 5 times if you want to see ALL. Any idea on that?

 

[DrSimon]

I'm not aware of any way to do that in a simple query. If you called the query from a form you could do fancy things.

 

[Golom]

You could write a function in a module like this

Public Function SponsorIsInString(strSponsors As String, ThisSponsor As String) As Boolean
    Dim aSponsors() As String
    Dim n           As Integer
    SponsorIsInString = False
    
    If Len(strSponsors) = 0 Then
        SponsorIsInString = True
            
    ElseIf InStr(strSponsors, ",") = 0 Then
        If Left$(ThisSponsor, Len(strSponsors)) = strSponsors Then _
        SponsorIsInString = True
    
    Else
    
        aSponsors = Split(strSponsors, ",")
        For n = LBound(aSponsors) To UBound(aSponsors)
            If Left$(ThisSponsor, Len(trim$(aSponsors(n)))) = Trim$(aSponsors(n)) Then
                SponsorIsInString = True
                Exit Function
            End If
        Next n
    End If
End Function

The user can enter something like "Jones, Smith, Wilson" and then your SQL looks like

WHERE SponsorIsInString ( [Enter Sponsor or Hit OK for All], fldSponsor )

 

[Zonie32]

somehow my sql was changed to this after i created the module. so, it works okay when the parameter pops up and i type in sponsor, sponsor, sponsor, but if i just hit OK for All, it is blank.

WHERE (((InStr([Enter Sponsor or Hit OK for All],[tblSponsors.Sponsor]))<>False))

 

[PHV]

You may try this:
WHERE InStr(Nz([Enter Sponsor or Hit OK for All],tblSponsors.Sponsor),tblSponsors.Sponsor)>0

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

 

[Zonie32]

Oh thank you so much PHV and Golom. Golom's module and PHV's final touch worked perfectly! You guys rock!