Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Excel query with a inputbox
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=Mi crosoft® 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change Excel query with a inputbox
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
InputBox Query | Excel Programming | |||
Change font size in Msgbox or Inputbox | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming | |||
Using InputBox to get desired date for a Query pulling data from Access into Excel | Excel Programming | |||
Using InputBox to change query criteria in Access | Excel Programming |