Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query - changing data using inputbox?
Can I change data with the use of an inputbox or some other method?
Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/2/2009 by Gerry Waldram ' ' Dim Year1 As String Year1 = InputBox("What YEAR are the Actual Figures From?", "Year of GL Use") With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=BCG;ServerName=BELSHIELD-DC. 1583;ServerDSN=BCG;ArrayFetchOn=1;ArrayBufferSize= 8;TransportHint=TCP:SPX;DecimalSymbol=.;Clien" _ ), Array ("tVersion=9.50.077.002;CodePageConvert=1252;AutoD oubleQuote=0;")) .CommandText = Array( _ "SELECT GLAFS.ACCTID, GLAFS.FSCSYR, GLAFS.FSCSDSG, GLAFS.NETPERD1, GLAFS.NETPERD2, GLAFS.NETPERD3, GLAFS.NETPERD4, GLAFS.NETPERD5, GLAFS.NETPERD6, GLAFS.NETPERD7, GLAFS.NETPERD8, GLAFS.NETPERD9, GLAFS." _ , _ "NETPERD10, GLAFS.NETPERD11, GLAFS.NETPERD12" & Chr(13) & "" & Chr(10) & "FROM GLAFS GLAFS" & Chr(13) & "" & Chr(10) & "WHERE (GLAFS.ACCTID'2728021000') AND (GLAFS.FSCSYR=Year1 _ ) AND (GLAFS.FSCSDSG='A')" & Chr(13) & "" & Chr(10) & "ORDER BY GLAFS.ACCTID" _ ) .Refresh BackgroundQuery:=False End With End Sub TIA Gerry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query - changing data using inputbox?
Wally,
What "data" are you looking to "change"? It appears that you are making an attempt to do this with your Year1 variable, but it doesn't appear that you have concatenated the variable properly into your SQL statment. "WHERE (GLAFS.ACCTID'2728021000') AND (GLAFS.FSCSYR=Year1)" should look like "WHERE (GLAFS.ACCTID'2728021000') AND (GLAFS.FSCSYR=" & Year1 & ")" I doubt that this will be an issue, but your Year1 String might need to be converted to a number, e.g. CInt(Year1) will convert the String into an Integer. Also, if the value is a String then you'll need to add the text qualifiers (i.e. the apostrophe or double quotes for text) around Year1; (GLAFS.FSCSYR=" & Year1 & ")" becomes (GLAFS.FSCSYR='" & Year1 & "')" . Best, Matthew Herbert "Wally" wrote: Can I change data with the use of an inputbox or some other method? Sub Macro3() ' ' Macro3 Macro ' Macro recorded 10/2/2009 by Gerry Waldram ' ' Dim Year1 As String Year1 = InputBox("What YEAR are the Actual Figures From?", "Year of GL Use") With Selection.QueryTable .Connection = Array(Array( _ "ODBC;DSN=BCG;ServerName=BELSHIELD-DC. 1583;ServerDSN=BCG;ArrayFetchOn=1;ArrayBufferSize= 8;TransportHint=TCP:SPX;DecimalSymbol=.;Clien" _ ), Array ("tVersion=9.50.077.002;CodePageConvert=1252;AutoD oubleQuote=0;")) .CommandText = Array( _ "SELECT GLAFS.ACCTID, GLAFS.FSCSYR, GLAFS.FSCSDSG, GLAFS.NETPERD1, GLAFS.NETPERD2, GLAFS.NETPERD3, GLAFS.NETPERD4, GLAFS.NETPERD5, GLAFS.NETPERD6, GLAFS.NETPERD7, GLAFS.NETPERD8, GLAFS.NETPERD9, GLAFS." _ , _ "NETPERD10, GLAFS.NETPERD11, GLAFS.NETPERD12" & Chr(13) & "" & Chr(10) & "FROM GLAFS GLAFS" & Chr(13) & "" & Chr(10) & "WHERE (GLAFS.ACCTID'2728021000') AND (GLAFS.FSCSYR=Year1 _ ) AND (GLAFS.FSCSDSG='A')" & Chr(13) & "" & Chr(10) & "ORDER BY GLAFS.ACCTID" _ ) .Refresh BackgroundQuery:=False End With End Sub TIA Gerry |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
MS Query - changing data using inputbox?
Thanks Matthew, works like a charm!!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Query to get external data | Excel Discussion (Misc queries) | |||
Changing query data source | Excel Discussion (Misc queries) | |||
Changing data source on ODBC query | Excel Discussion (Misc queries) | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming |