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

See my reply in your original thread

Tim

--
Tim Williams
Palo Alto, CA


"ACase" <ACase@discussions.microsoft.com> wrote in message
news:110CCE68-D0D9-4928-8464-D61DFF9165CF@microsoft.com...
> 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
>

"Tim Williams" <timjwilliams at gmail dot com> wrote in message
news:%23CKtr7GxFHA.2728@TK2MSFTNGP14.phx.gbl...
> 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" <ACase@discussions.microsoft.com> wrote in message
> news:110CCE68-D0D9-4928-8464-D61DFF9165CF@microsoft.com...
>> 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
>>
>
>