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

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

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