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

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

MS Query - changing data using inputbox?
 

Thanks Matthew, works like a charm!!