Searching Between Dates

[rededdie]

I am new to this site and I have a question. I have a db that I need to be
able to search BETWEEN Dates and show them on my subform.. The problem is
that I can get it to search but only by one date. I need it to pull up the
date and the dates BETWEEN... Lets say I have a box and put 4-7-07.. Then the
other box 4-15-07.. I want it to pull the dates in the box and the ones
BETWEEN.... Is this possible? I know it is but I need a little assistance...
I have added the code and can someone see what I am missing.... Thanks for
your help...

This is what it says I am Missing ), ], ( missing operator) in
expression1'AND ([DateFrom] Between #2007-04-17#AND#2008-04-18#OR[DateTo]
Between#2007-04-17#AND#2008-04-18#)'.
This is how I rewrote it with the suggestion above.... Thanks for thehelp....

If Nz(Me.DateFrom, "") <> "" And Nz(Me.DateTo, "") <> "" Then
strWhere = strWhere & " AND ([DateFrom] Between #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "# AND #" & _
Format(Me.DateTo, "yyyy-mm-dd") & "# OR ([DateTo] Between #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "# AND #" & _
Format(Me.DateTo, "yyyy-mm-dd") & "#)" <<<<<<<<< Do I need another ) behind
the dd") right here
Else
strWhere = strWhere & " AND ([DateFrom] = #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "#" & _
" OR [DateTo]=#" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "#)"
End If

 

[Steve Sanford]

Add this statement after the "End If" to see the where clause:

' for debugging
Debug.Print strWhere

There is a closing paren missing before the "OR". If you break it up like
this, it is easy to see:

AND ([DateFrom] Between #2006-07-01# AND #2007-07-01# '<<<<<
OR ([DateTo] Between #2006-07-01# AND #2007-07-01#)



Try this:

'============================
If Nz(Me.DateFrom, "") <> "" And Nz(Me.dateTo, "") <> "" Then
strWhere = strWhere & " AND ([DateFrom] Between #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "# AND #" & _
Format(Me.dateTo, "yyyy-mm-dd") & "#) OR ([DateTo] Between #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "# AND #" & _
Format(Me.dateTo, "yyyy-mm-dd") & "#)"
Else
strWhere = strWhere & " AND ([DateFrom] = #" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "#" & _
" OR [DateTo]=#" & _
Format(Me.DateFrom, "yyyy-mm-dd") & "#)"
End If
'============================



HTH