MS Query - changing data using inputbox?

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
 
M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top