MS Query - changing data using inputbox?

[Wally]

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;AutoDoubleQuote=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

 

[Matthew Herbert]

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]

Thanks Matthew, works like a charm!!