VBA SQL Reading data from a column until empty cell.

[dcg38524]

Hello,

Need your help on a VBA code modification.

We have an existing excel SQL query that works, but there are group names hard-coded which I wanted to revisit. As you know this works great if nothing changes but can be very awkward and inconvenient when changes are made frequently.

Just was a little curious on how hard it would be to change the code, I searched the web and couldn't find any clear solutions, so I am reaching out for help.

If all possible I would like to change the current code to read these group names from the existing workbook

• Tab called "valid names"
• Starting from column "A2"
• Until it reaches an empty cell.

Our code currently looks like below: 'Please Don't laugh,'
SELECT "ASSIGNED_TO_GROUP_, ASSIGNED_TO_INDIVIDUAL_, TICKET_ID_ , etc...."
FROM WAREHOUSE_MANAGEMENT
WHERE ASSIGNED_TO_INDIVIDUAL_ <> 'John'
AND ASSIGNED_TO_GROUP_ IN ( 'GROUP1' , 'GROUP2', 'GROUP3', 'GROUP4', 'GROUP5')
ORDER BY
TICKET_ID_

 

[Jerry Sullivan]

Hi Don,

SQL's IN operator will work with one item. The reason your getting an error is because if there's only one item, then the transposed arrGroups will not be an Array. The Join function requires the first argument passed to it to be an array.

One way to get your code to work with either one or multiple items is...

 arrGroups = Application.Transpose(arrGroups)
 If IsArray(arrGroups) Then
   sGroups = "'" & Join(arrGroups, "','") & "'"
 Else
   sGroups = "'" & arrGroups & "'"
 End If

 

[dcg38524]

Hi Jerry,

Always a pleasure to hear from you sir . Your array modification code worked great, the SQL query now can function using one or two group entries.

I really can't thank you enough, take care.

Kind regards,
Don