SQL in VBA use values in a worksheet?

[TheWennerWoman]

Hello,

Hope someone can help.

I have a SQL view that I am calling from VBA. Something basic like

SELECT * FROM TheWennerWomans_View

The view contains my qualifiers (so, for example, it contains a field called NominalCode) which has about forty values.

Instead of NominalCode being stored in the view, I've been asked to store it on a separate worksheet so that the users can add / delete nominal codes as necessary.

So my SQL code would now need to be something like

SELECT * FROM TheWennerWomans_View WHERE NominalCode IN (SELECT NominalCode FROM Sheet1)

Has anyone done anything similar? Hope this makes sense

 

[RoryA]

You'd need a function to build up a list of the values from the range. Are the codes text or numbers in your database?

 

[RoryA]

You can use a function like this:

Function GetStringInList(CriteriaRange As Range, Optional TextDelim As String = "") As String
   Dim Cell As Range
   For Each Cell In CriteriaRange.Cells
      Dim loopText As String
      If Len(Cell.Value) > 0 Then loopText = loopText & "," & TextDelim & Replace$(Cell.Value, "'", "''") & TextDelim
   Next Cell
   ' now strip off leading comma
   GetStringInList = Mid$(loopText, 2)
End Function

If the values are numeric, you'd just use getstringinlist(range("A1:A10")) or if textual, use getstringinlist(range("A1:A10"), "'") for example.

 

[TheWennerWoman]

Thanks for that, how would I incorporate that into my SQL code?

 

[RoryA]

You'd build up your SQL string using code like:

SQLString = "SELECT * FROM TheWennerWomans_View WHERE NominalCode IN (" & getstringinlist(range("A1:A10"), "'") & ")"

for example.

 

[TheWennerWoman]

You'd build up your SQL string using code like:

SQLString = "SELECT * FROM TheWennerWomans_View WHERE NominalCode IN (" & getstringinlist(range("A1:A10"), "'") & ")"

for example.

Very good, thank you very much