I have the following query which works when run directly in Access.

SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, tbl_Pricing_Tiers.EffectiveDate,
tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, tbl_Pricing_Tiers.Pricing_Ven_ID
FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers
WHERE (((tbl_Pricing_Vendors.DIV) LIKE '*') AND ((tbl_Pricing_Vendors.DEPT)
LIKE '*')
AND (tbl_Pricing_Vendors.Pricing_Ven_ID = tbl_Pricing_Tiers.Pricing_Ven_ID))
ORDER BY tbl_Pricing_Vendors.DEPT, tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate DESC , tbl_Pricing_Tiers.cost DESC;

The above query runs and returns all records.

But when I run it from Excel VBA, it runs all the way through but returns
zero records.
The code is as below.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConn, strDB, strSQL, strData As String
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
strDB = "C:\Docs\SOMEDATA.mdb"
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & strDB & ";"
strSQL = "SELECT tbl_Pricing_Vendors.DIV, tbl_Pricing_Vendors.DEPT, "
strSQL = strSQL & "tbl_Pricing_Vendors.VendorName,
tbl_Pricing_Tiers.EffectiveDate, "
strSQL = strSQL & "tbl_Pricing_Tiers.Min, tbl_Pricing_Tiers.Max, "
strSQL = strSQL & "tbl_Pricing_Tiers.cost,
tbl_Pricing_Vendors.Pricing_Ven_ID, "
strSQL = strSQL & "tbl_Pricing_Tiers.Pricing_Ven_ID "
strSQL = strSQL & "FROM tbl_Pricing_Vendors, tbl_Pricing_Tiers "
strSQL = strSQL & "WHERE (((tbl_Pricing_Vendors.DIV) LIKE '" & strDiv &
"') AND "
strSQL = strSQL & "((tbl_Pricing_Vendors.DEPT) LIKE '" & strDept & "')
AND "
strSQL = strSQL & "(tbl_Pricing_Vendors.Pricing_Ven_ID =
tbl_Pricing_Tiers.Pricing_Ven_ID)) "
strSQL = strSQL & "ORDER BY tbl_Pricing_Vendors.DEPT,
tbl_Pricing_Vendors.VendorName, "
strSQL = strSQL & "tbl_Pricing_Tiers.EffectiveDate DESC,
tbl_Pricing_Tiers.cost DESC;"
conn.Open (strConn)
cmd.CommandText = strSQL
cmd.ActiveConnection = conn
Set rs = cmd.Execute
msgbox(rs.RecordCount)


Is there anything I need to do to make this work?

Thanks much.