Feeding Worksheet Data Into a Query

[Tangier]

If I have 4 values that reside in a worksheet in cells F1:F4,

how do I take these values and specify these as criteria in my where
clause:

WHERE ID IN (F1:F4)

I need a comma separated list of IDs so that the T-SQL statement can
be executed correctly,

WHERE ID IN (F1,F2,F3,F4)

 

[ND Pard]

There's always more than 1 way to skin a cat ...

One way to solve your problem would be to dim 5 variables.
Example:
(I am going to assume your values are string values.)

Dim strA As String, strB As String, strC As String, D As String
Dim strWhereSQL As String

strA = Range("F1").Value
strB = Range("F2").Value
strC = Range("F3").Value
strD = Range("F4").Value

strWhereSQL = "Where ID in ('" & strA & "','" & strB & _
"','" & strC & "','" & strD & "')"

I hope that is of some help to you.

Good Luck

 

[Tangier]

There's always more than 1 way to skin a cat ...

One way to solve your problem would be to dim 5 variables.
Example:
(I am going to assume your values are string values.)

Dim strA As String, strB As String, strC As String, D As String
Dim strWhereSQL As String

strA = Range("F1").Value
strB = Range("F2").Value
strC = Range("F3").Value
strD = Range("F4").Value

strWhereSQL = "Where ID in ('" & strA & "','" & strB & _
  "','" & strC & "','" & strD & "')"

I hope that is of some help to you.

Good Luck









- Show quoted text -

THANK YOU! But now the question is, if I have cells to get data from
F2:F50, how do I generate a comma separated list to put into the
WHERE CHildID in ( comma separated list here )

 

[ND Pard]

Well why didn't you say so:

Sub Try_This()
'Assumes your list starts on F2 and that they are Sting values
'(You never did let us know if you're working with text or numbers)

dim strWhereSQL as string
Range("F2").Select
Do while ActiveCell.Value > ""
If strWhereSQL > "" Then
strWhereSQL = strWhereSQL & ",'" & ActiveCell.Value & "'"
Else
strWhereSQL = "Where ID In '" & ActiveCell.Value & "'"
End If
ActiveCell.Offset(1).Select
Loop
strWhereSQL = strWhereSQL & ")"
End Sub

Good Luck.

 

[Tangier]

Well why didn't you say so:

Sub Try_This()
'Assumes your list starts on F2 and that they are Sting values
'(You never did let us know if you're working with text or numbers)

dim strWhereSQL as string
  Range("F2").Select
Do while ActiveCell.Value > ""
  If strWhereSQL > "" Then
    strWhereSQL = strWhereSQL & ",'" & ActiveCell.Value & "'"
  Else
      strWhereSQL = "Where ID In '" & ActiveCell.Value & "'"
  End If
  ActiveCell.Offset(1).Select
Loop
  strWhereSQL = strWhereSQL & ")"
End Sub

Good Luck.






- Show quoted text -

thanx!!!!!!!!!!!!!!!1

 

[Tangier]

Well why didn't you say so:

Sub Try_This()
'Assumes your list starts on F2 and that they are Sting values
'(You never did let us know if you're working with text or numbers)

dim strWhereSQL as string
  Range("F2").Select
Do while ActiveCell.Value > ""
  If strWhereSQL > "" Then
    strWhereSQL = strWhereSQL & ",'" & ActiveCell.Value & "'"
  Else
      strWhereSQL = "Where ID In '" & ActiveCell.Value & "'"
  End If
  ActiveCell.Offset(1).Select
Loop
  strWhereSQL = strWhereSQL & ")"
End Sub

Good Luck.






- Show quoted text -

ok sorry for all the questions, but how do I call this subroutine
within another subroutine which outputs the query?

PS, I am working with Child IDs, so in this case, numeric data.

 

[ND Pard]

To call the Try_This() subprocedure from a different subprocedure, you enter
the following line in the different subprocedure:

Call Try_This

Good Luck.

Sub Try_This()
'Assumes your list starts on F2 and that they are Numeric values

dim strWhereSQL as string
Range("F2").Select
Do while ActiveCell.Value > ""
If strWhereSQL > "" Then
strWhereSQL = strWhereSQL & "," & ActiveCell.Value
Else
strWhereSQL = "Where ID In (" & ActiveCell.Value
End If
ActiveCell.Offset(1).Select
Loop
strWhereSQL = strWhereSQL & ")"
End Sub