Avatar of cebu1014
cebu1014

asked on 

VBA Select Statement and/or

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
Microsoft Excel

Avatar of undefined
Last Comment
Steve
ASKER CERTIFIED SOLUTION
Avatar of Steve
Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft Excel
Microsoft Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

144K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo