Passing a Function (String) to a Where clause

[Baby Face Lee]

Hello (again),
I'm attempting to pass a string of text to the Where Clause in a query via a
Public Function. Here's the code:

Public Function GetUAB() As String
Set ctrlUAB = [Forms]![frm_BasicStats]![cboUAB]
Set ctrlchkUAB = [Forms]![frm_BasicStats]![chkUAB]

If ctrlchkUAB = -1 And ctrlUAB = 1 Then
stUAB = "100 Or 101 Or 110 Or 111"
GetUAB = stUAB
ElseIf ctrlchkUAB = -1 And ctrlUAB = 2 Then
stUAB = "011 Or 010 Or 110 Or 111"
GetUAB = stUAB
ElseIf ctrlchkUAB = -1 And ctrlUAB = 3 Then
stUAB = "001 Or 101 Or 011 Or 111"
GetUAB = stUAB
ElseIf ctrlchkUAB = 0 Then
GetUAB = "000 Or 001 Or 010 Or 011 Or 100 Or 101 Or 110 Or 111"
End If

End Function

No matter what I do, the query doesn't return any records. It's OK if I
type each of the above strings into the criteria box but the 'GetUAB()'
function call doesn't go away and get the appropriate string automatically.
I've tried using a LongInteger instead of a string without any joy either. I
do hope you can help as I'm getting very frustrated with this now!!!

Kind Regards,

Lee

 

[Douglas J. Steele]

How are you trying to use the function? If it's something like:

strSQL = strSQL & " WHERE MyField = " & GetUAB()

that would be invalid SQL. When you've typed that into the query's Criteria
cell, take a look at the SQL that's generated: it'll be something like WHERE
MyField = 100 Or MyFIeld = 101 Or MyField = ...

Try changing your function to

Public Function GetUAB() As String
Set ctrlUAB = [Forms]![frm_BasicStats]![cboUAB]
Set ctrlchkUAB = [Forms]![frm_BasicStats]![chkUAB]

If ctrlchkUAB = -1 And ctrlUAB = 1 Then
stUAB = "(100, 101, 110, 111)"
GetUAB = stUAB
ElseIf ctrlchkUAB = -1 And ctrlUAB = 2 Then
stUAB = "(011, 010, 110, 111)"
GetUAB = stUAB
ElseIf ctrlchkUAB = -1 And ctrlUAB = 3 Then
stUAB = "(001, 101, 011, 111)"
GetUAB = stUAB
ElseIf ctrlchkUAB = 0 Then
GetUAB = "(000, 001, 010, 011, 100, 101, 110, 111)"
End If

End Function

and change your usage to:

strSQL = strSQL & " WHERE MyField IN " & GetUAB()