Between Dates Criteria

[Apprentice]

Can’t' seem to get it. I have two fields: Start_Date and End_Date. Both are
Short Date Format.

Note: Both date fields are previously calculated dates from the original
"Review_Date field. Like, Review_Date+10 is the Start_Date and Review_Date+20
is the End_Date.

I want to use a between dates criteria using both fields. I have researched
and tried so many combinations to list here without success.

**** The dates I ask for are never correct, or within the parameter entered.
Either one or the other is off.

I have even tried two parameters, [Enter Start Date] on the End_Date field
criteria -
and [Enter End Date] on the End_Date field criteria

Is this something simple I'm just missing?

 

[KARL DEWEY]

Try this in the query design view --
In a blank column of the grid in the Field row type --
My_Date_Check: CVDate([Enter date to check])

In the criteria row below that type this --
Between [Start_Date] AND [End_Date]

 

[vanderghast]

To work, it must be real date_time datatype, NOT string datatype. Since you
mention a format, a format result is a string! and for strings, the order is
like "A", "AZZZ", "B", and also "1", "1999", "2" ( while 1999> 2,
"1999" < "2", that is a difference between numbers and strings).


Try to convert your strings into date datatype, with CDate (in SQL view) :


.... WHERE (someOtherCriteria)
AND (dateTimeField BETWEEN CDate(stringOfDate1) AND
CDate(stringOfDate2))
AND (yetSomeOtherCriteriaAgain)


Vanderghast, Access MVP