Variable in a select query

Super Suarez

Registered User.
Local time
Today, 09:24
Joined
Jul 10, 2013
Messages
36
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.
 
Try this:
List387.RowSource = "SELECT " & findequipstr() & " FROM Equipment"
 
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?
 
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)
Code:
Public Function findequipstr()
  If IsNull(List371.Value) Then
    findequipstr=Null
Exit Function
  End If

   findequipstr = List371.Value
End Function

Run the query.
 

Users who are viewing this thread

Back
Top Bottom