Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming for a Dynamic 'Where' clause
Hello
I have a form, which lets a user select a report and combo boxes to select its criteria(s). Example: "New Business Report" (State = 'NY', Region = North America) Once a user selects the report and sets the criteria the 'Where' clause is created dynamically in the code. Example - In the code I have a series of statements which will check each and every combo box. If the combo box is null it gets ignored, if it is populated (State = NY) then the criteria is added to the 'Where' clause. If Not IsNull(Me.cmb_Country) Then strWhere = strWhere & "([Country] = """ & Me.cmb_Country End If I now want to add a date range as a criteria option. I don't know how to write the syntax though. My 2 combo boxes on the user form are cmb_StartDate & cmb_EndDate. What is wrong with this syntax? If Not IsNull(Me.cmb_StartDate) and IsNull (Me.cmb_EndDate) Then strWhere = strWhere & "([Date] = between """ & Me.cmb_StartDate and Me.cmb_EndDate. End If Any help would be much appreciated. Thanks AC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming for a Dynamic 'Where' clause
See my reply in your original thread
Tim -- Tim Williams Palo Alto, CA "ACase" wrote in message ... Hello I have a form, which lets a user select a report and combo boxes to select its criteria(s). Example: "New Business Report" (State = 'NY', Region = North America) Once a user selects the report and sets the criteria the 'Where' clause is created dynamically in the code. Example - In the code I have a series of statements which will check each and every combo box. If the combo box is null it gets ignored, if it is populated (State = NY) then the criteria is added to the 'Where' clause. If Not IsNull(Me.cmb_Country) Then strWhere = strWhere & "([Country] = """ & Me.cmb_Country End If I now want to add a date range as a criteria option. I don't know how to write the syntax though. My 2 combo boxes on the user form are cmb_StartDate & cmb_EndDate. What is wrong with this syntax? If Not IsNull(Me.cmb_StartDate) and IsNull (Me.cmb_EndDate) Then strWhere = strWhere & "([Date] = between """ & Me.cmb_StartDate and Me.cmb_EndDate. End If Any help would be much appreciated. Thanks AC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Programming for a Dynamic 'Where' clause
"Tim Williams" <timjwilliams at gmail dot com wrote in message ... See my reply in your original thread Tim Which I don't see now, but it was something like If Not IsNull(Me.cmb_StartDate) and not IsNull (Me.cmb_EndDate) Then strWhere = strWhere & " ([Date] between '" & _ Me.cmb_StartDate & "' and '" & _ Me.cmb_EndDate & "') " End If You really need to tell us which database you're using, otherwise it's diffcult to check the SQL syntax around the dates. When you're done building the SQL string use debug.print and pate the SQL into a query tool to check it. Also - if possible - change that "Date" column name to something else. Tim. "ACase" wrote in message ... Hello I have a form, which lets a user select a report and combo boxes to select its criteria(s). Example: "New Business Report" (State = 'NY', Region = North America) Once a user selects the report and sets the criteria the 'Where' clause is created dynamically in the code. Example - In the code I have a series of statements which will check each and every combo box. If the combo box is null it gets ignored, if it is populated (State = NY) then the criteria is added to the 'Where' clause. If Not IsNull(Me.cmb_Country) Then strWhere = strWhere & "([Country] = """ & Me.cmb_Country End If I now want to add a date range as a criteria option. I don't know how to write the syntax though. My 2 combo boxes on the user form are cmb_StartDate & cmb_EndDate. What is wrong with this syntax? If Not IsNull(Me.cmb_StartDate) and IsNull (Me.cmb_EndDate) Then strWhere = strWhere & "([Date] = between """ & Me.cmb_StartDate and Me.cmb_EndDate. End If Any help would be much appreciated. Thanks AC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PERCENTILE with an IF Clause | Excel Worksheet Functions | |||
IF Clause | Excel Worksheet Functions | |||
"Between" in an IF clause | Excel Discussion (Misc queries) | |||
Creating/programming an Excel dynamic graph | Excel Programming | |||
if then clause in a cell | Excel Programming |