Convert SQL code to VBA code

[Ryan H]

Hello all,

I have been trying to solve this issue for a while now and could really use some help!
Basically, I am trying to convert a SQL code into VBA but I'm having some issues with the piece below:

FROM HISTORY.dbo.tbl_Data
 
Where
Week_Ending = 'WE080611'
AND Facility IN ('Hartford, CT')
AND (Ledgend LIKE 'DEL')
AND [Cust_#] IN ('12345')
 
) i
 
GROUP BY DayofWeek
) j

In my excel file, I have a few drop down boxes and list boxes which I use to update the fields (except for the Ledgend part). I've already put together the majority of the code I'm just stuck on this one piece. Any help would be appreciated! Here's what I have right now..

"FROM HISTORY.dbo.tbl_Data" & _
 
"WHERE [Facility] = '" & Facility & "' and [Week_Ending] = '" & WeekEnd & "' and (Ledgend LIKE 'DEL') AND [Route #] IN " & _
"( SELECT DISTINCT [Route #] FROM HISTORY.dbo.tbl_Data WHERE " & _
WhereStr & " AND (Ledgend LIKE 'DEL') " & _
 
") k" & _
"GROUP BY DayofWeek" & _

[ADezii]

The Basic SQL would look something like this:

SELECT * FROM tbl_Data
WHERE tbl_Data.Week_Ending="WE080611" 
AND tbl_Data.Facility In ('Hartford, CT') 
AND tbl_Data.Ledgend Like "*Del*" 
AND tbl_Data.[Cust_#] In ('12345');

[Ryan H]

Thanks for responding ADezii, I already have the SQL code in place though and it right now it's working like it should. It's when I'm trying to convert that code into VBA that's the issue. I'm by no means an expert in VBA but I think I've narrowed down the issue to the piece of code above.

[ADezii]

Here is a functional example of how you can convert the SQL Code to VBA:

Dim strSQL As String
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
strSQL = "SELECT * FROM tbl_Data WHERE tbl_Data.Week_Ending = 'WE080611' AND " & _
         "tbl_Data.Facility In ('Hartford, CT') AND tbl_Data.Ledgend Like '*Del*' " & _
         "AND tbl_Data.[Cust_#] In ('12345');"
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
 
rst.MoveFirst
 
With rst
  Do Until .EOF
    Debug.Print ![Week_Ending], ![Facility], ![Ledgend], ![Cust_#], ![DayOfWeek]
     .MoveNext
  Loop
End With
 
If Not rst Is Nothing Then
  rst.Close
    Set rst = Nothing
End If