Enter inputbox to sql query

[wally1961]

Need some help with this code. Trying to enter CoName & Year1 into the VBA code below

Year1 = InputBox("What YEAR are the Actual Figures From?", "Year of GL Use")
CoName = Range("CoName").Value

With Selection.ListObject.QueryTable
.Connection = _
"ODBC;DRIVER=SQL Native Client;SERVER=W2012-SQL;UID=sa;PWD=sa#12345;DATABASE='& CoName &';"
.CommandText = Array( _
"SELECT GLAFS.ACCTID, GLAMF.ACCTDESC, 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 AA1517.dbo.GLAFS GLAFS, AA1517.dbo.GLAMF GLAMF" & Chr(13) & "" & Chr(10) & "WHERE GLAMF.ACCTID = GLAFS.ACCTID AND ((GLAFS.ACCTID>'29%') AND (GLAFS.FSCSYR= '& Year1 &') " _
, "AND (GLAFS.FSCSDSG='A'))" & Chr(13) & "" & Chr(10) & "ORDER BY GLAFS.ACCTID")
.Refresh BackgroundQuery:=False
End With

Thanks in Advance

Gerry

[Izandol]

You must concatenate the variables to your text with double quotation marks:

Please Login or Register  to view this content.