Variable in a select query

[Super Suarez]

Hi

Im trying to pass a list box variable in a select query. I understand you cannot pass a variable directly but have to pass it through a function. I may be wrong in this, but whatever I do I cannot get it to work. Here's my code:-

Public Sub GetEquipment()
List387.RowSourceType = "Table/Query"
List387.RowSource = "SELECT findequipstr() FROM Equipment"
End Sub

Public Function findequipstr() As String
If IsNull(List371.Value) Then GoTo function_end
findequipstr = List371.Value
function_end:
End Function


If I MsgBox(findequipstr()) within my Getequipment function, the variable is messaged, so I can't quite see what I've done wrong. Hope someone can help.

Thanks

Hope someone can help.

 

[Mihail]

Try this:
List387.RowSource = "SELECT " & findequipstr() & " FROM Equipment"

 

[Super Suarez]

Thanks for the reply, but that didn't work for me. The strings do have spaces in them so it may be that. I have to put square brakets in perhaps but not sure where?

 

[Mihail]

The faster solution for you:
Create a query.
In the criteria row write this: findequipstr()

I tweak a little bit your function (not strongly necessary)

Public Function findequipstr()
  If IsNull(List371.Value) Then
    findequipstr=Null
Exit Function
  End If

   findequipstr = List371.Value
End Function

Run the query.