strWhere = filter 'between' syntax?

[Maarkr]

Trying to do a form filter query to get the query to view records between the
date entered and 15 days later...

strWhere = "shDate= '" & Me.Combo1 & "'"

gives me a simple date filter, but I've struck out trying the parens and the
'between x AND y ' expression

?? plain language = between combo1date and combo1date + 15

thx

 

[Dirk Goldgar]

Trying to do a form filter query to get the query to view records between
the
date entered and 15 days later...

strWhere = "shDate= '" & Me.Combo1 & "'"

gives me a simple date filter, but I've struck out trying the parens and
the
'between x AND y ' expression

?? plain language = between combo1date and combo1date + 15

Is shDate a date/time field? If it is, you really should be using the date
delimiter # instead of the text delimiter ' to surround your date literals.
You also should make sure your date literals are formatted into either US
date format (MM/DD/YYYY) or an unambiguous international standard format.
Try this:

strWhere = _
"shDate Between " & Format(Me.Combo1, "\#mm/dd/yyyy\#") & _
" AND (" & Format(Me.Combo1, "\#mm/dd/yyyy\#") & " + 15)"

That version lets the query processor do the addition. An alternative would
be to add the 15 days yourself:

strWhere = _
"shDate Between " & Format(Me.Combo1, "\#mm/dd/yyyy\#") & _
" AND " & Format(CDate(Me.Combo1) + 15, "\#mm/dd/yyyy\#")

 

[Maarkr]

thanks...

Is shDate a date/time field? YES

Try this:

strWhere = _
"shDate Between " & Format(Me.Combo1, "\#mm/dd/yyyy\#") & _
" AND (" & Format(Me.Combo1, "\#mm/dd/yyyy\#") & " + 15)"

The second version didn't work. I still have an issue where it computes
correctly sometimes, then burps and will show records starting on the 26th of
the month when I've typed in another date, like the 1st. Debug shows
strWhere has the proper dates, like the 1st thru 15th, but why the form shows
the 26th I don't know... I'll need to look at the rest of the form to see
what's going on.

 

[Dirk Goldgar]

thanks...




The second version didn't work.

I may have made a mistake. That was untested air code.

I still have an issue where it computes
correctly sometimes, then burps and will show records starting on the 26th
of
the month when I've typed in another date, like the 1st. Debug shows
strWhere has the proper dates, like the 1st thru 15th, but why the form
shows
the 26th I don't know... I'll need to look at the rest of the form to see
what's going on.

That's odd. I'd need to see the specific where clause and exactly what's
being returned, to figure it out.