where condition on report 1

[pduncan]

I have a report that is based on the selection of 3 combo boxes. The SQL is a mess, but it works. Being that the data set is small, having this looooong SQL statement isnt that much of an issue. I need to further filter the results by date. I have two combo boxs (cboStartDate and cboEndDate) that when clicked, gets the date value from the active x calendar control. When I try to add the "between yada yada and bla bla" I get an error saying the query is too complicated, bla bla bla.
So I thought - what if I leave the query as is and just use the WHERE portion of the docmd.openreport.
I tried creating a variable (strWhere)

Dim strWhere As String
strWhere = "where " & [date] & "is Between #" & cboStartDate & "# and #" & cboEndDate & "#"

    DoCmd.OpenReport "rptActivityReport", , , strWhere

but this doesnt work

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.

 

[dhookom]

Try:

strWhere = "[date] Between #" & cboStartDate & "# and #" & cboEndDate & "#"

You do realize that a field named date isn't ideal since date is also the name of a function?



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[pduncan]

Duane - that did it - thanks. And I will also be changing the name of my date field. Star to you.

Thanks, PDUNCAN
Memphis, TN - USA
When I die, I want to die like my grandfather-- who died peacefully in
his sleep. Not screaming like all the passengers in his car.