Change Excel query with a inputbox

[clintonw_123]

I have a Query running in Excel at it works perfectly, I need to add a
popup box for the user to enter a date so they don't have to enter MS
query but can stay in Excel.

Now the data I'm pulling is from a .SQL database and I have a date
field that will change each time someone runs the file Example:
2008-05-02.

I have tried to add a inputbox but the data is not returned when I
enter the date in the input box

Any help would be greatly appreiated. I have included my simple query
below.

Range("L3").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DRIVER=SQL
Server;SERVER=Hidden;UID=hidden;PWD=hidden);APP=Microsoft®
Query;WSID=Hidden" _
, Destination:=Range("L3"))
.CommandText = Array( _
"SELECT ae_dt1.field1, ae_dt1.field2, ae_dt1.field3" & Chr(13)
& "" & Chr(10) & _
"FROM Xtender.sysop.ae_dt1 ae_dt1" & Chr(13) & "" & Chr(10) &
_
"WHERE (ae_dt1.field4='2008-05-02') AND
(ae_dt1.field7='CLINTON')")
.Name = "Query from Roc_Checker_1"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[ward376]

You want to use a parameter for the date. You can only use parameters
in Query, not in the Query Wizard.

instead of: WHERE (ae_dt1.field4='2008-05-02')
try: WHERE (ae_dt1.field4=?)
Excel should prompt the user for the criteria value (parameter).

To create a parameter in the msquery interface, use square brackets
(with or without a default) as the value for the date field criteria.
To create a parameter in the sql statment, substitute a question mark
for the date value.

Cliff Edwards