troubleshooting Question

VBA Select Statement and/or

Avatar of cebu1014
cebu1014 asked on
Microsoft Excel
1 Comment1 Solution338 ViewsLast Modified:
I am trying to modify this query that was created by another to run in VBA. I get error 13 type mismatch . What I want to do is add a filter to include >000000050000000 and < 000000500099999 to what is listed already. The overflow line in select statement is causing me problems as well.


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=tap;ServerName=SHCPSERVER.1583;ServerDSN=TAP;ArrayFetchOn=1;ArrayBufferSize=8;TransportHint=TCP;DecimalSymbol=.;ClientVersion" _
        ), Array("=8.60.192.030;CodePageConvert=1252;AutoDoubleQuote=0;")), Destination _
        :=Range("A1"))
        .CommandText = Array( _
       "SELECT ITEM.NBR, ITEM.DESCR, ITEM.USR_DEF_4, ITEM.PRIME_VEND, ITEM.CAT, ITEM.SUB_CAT, ITEM.PRC_1_1, ITEM.PRC_2_1, ITEM.AVG_COST, ITEM.STD_COST, ITEM.USR_DEF_1, ITEM.USR_DEF_3, ITEM.USR_FMT_NUM_2  FROM ITEM ITEM  WHERE (ITEM.NBR<'000000000999999'" _
        , ")  ORDER BY ITEM.NBR")

        .Name = "ITEMIMAS"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\kip.TAP\Application Data\Microsoft\Queries\ITEMIMAS.dqy"
        .Refresh BackgroundQuery:=False
    End With
ASKER CERTIFIED SOLUTION
Steve
Cost Accountant
Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros