MS Query - changing data using inputbox?

  • Thread starter Thread starter Wally
  • Start date Start date
W

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
 
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
 
Back
Top