I am sorry, my mistake. you are both right.
% does work. Somehow (I do not know how) I have used * and ? for wild cards
and have always worked.
Sorry about that.
BTW, LIKE * is to indeed return everything.
But in this case * is the value fo a string variable, which could be either
set to a specific value or * if the user wanted to report on all divisions.
The query works correctly in Access directly with * so I assumed that it
should work in VBA calling from Excel.
"Sam Wilson" wrote:
> It is true... why are you looking for LIKE '*' anyway? That will return
> everything?
>
>
> "omsoft" wrote:
>
> > I do not believe that is true.
> >
> > "Harald Staff" wrote:
> >
> > > If I read this right; wildcard in SQL is % , not *
> > >
> > > Best wishes Harald
> > >
> > > "omsoft" <(E-Mail Removed)> wrote in message
> > > news:B5400FB8-77B4-4367-A44D-(E-Mail Removed)...
> > > >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.
> > >
> > >
|
Similar Threads
