Pass Function to Query Criteria but need "OR"

[GPM4663]

Hi Everyone,
I'm using the code to get a variable that can be used in the criteria of a query. My problem is how its evaluated in the query i.e. I'm trying to build a criteria expression like ' "MCR" or "HL" ' but the query is seeing it as ' "MCR OR HL" ' . Is there some way to pull the "OR" out of the string? Thanks in advance for any help.

GPM

Public Function GetSite() As Variant
Module1.strSite = ""

If Module1.blnManchester = True Then Module1.strSite = Module1.strSite & "MCR or "
If Module1.blnLivingston = True Then Module1.strSite = Module1.strSite & "LIV or "
If Module1.blnHarlow = True Then Module1.strSite = Module1.strSite & "HL or "

If Len(Module1.strSite) > 0 Then Module1.strSite = Left(Module1.strSite, Len(Module1.strSite) - 4)

GetSite = Module1.strSite

End Function

 

[Remou]

You could go on to build the query, for example:

strSQL= "SELECT Blah FROM tblT WHERE " & GetSite

'Then
Me.RecordSource=strSQL

'Or
Set qdf=CurrentDB.QueryDefs("SomeQuery")
qdf.SQL=strSQL

Otherwise, you could refer to the checkboxes in the query:

=Iff(Forms!frmF!blnManchester=True,"MCR","")
=Iff(Forms!frmF!blnManchester=True,"LIV","")

 

[GPM4663]

Thanks Remou,
I'll give the QueryDefs a go.

thanks for the help,

GPM

 

[JBinQLD]

Or you could give this simple mod a go....

Public Function GetSite() As [red]string[/red]
    Module1.strSite = ""
    
    If Module1.blnManchester = True Then Module1.strSite = Module1.strSite & "[red]'[/red]MCR[red]'[/red] or "
    If Module1.blnLivingston = True Then Module1.strSite = Module1.strSite & "[red]'[/red]LIV[red]'[/red] or "
    If Module1.blnHarlow = True Then Module1.strSite = Module1.strSite & "[red]'[/red]HL[red]'[/red] or "

    If Len(Module1.strSite) > 0 Then Module1.strSite = Left(Module1.strSite, Len(Module1.strSite) - 4)
    
    GetSite = Module1.strSite

End Function

Let us know how you go...

JB

 

[Remou]

Thanks, JBinQLD. I missed that altogether