date functions

  • Thread starter Thread starter Kurt Neumann
  • Start date Start date
K

Kurt Neumann

i have a table with a date/time field
i want to query it with a " between [start date] and [end date]
criteria (inclusive of both dates). it won't work, i think because the
field is actually a date and a time, not just a date. if i specify the [end
date] as {the DAY AFTER the end date] it seems to work ok. Is there a
criteria that works better or is there a way to force the field to contain
only the date.
i frequently want the cirteria to be one day. ie. between #9/23/04# and
#9/23/04# to selcet today's records
i default the table input to today's date.


thanks in advance
 
Try

WHERE DateValue(MyField) Between [start date] and DateAdd("d", 1, [end
date])

(or maybe that should be

WHERE DateValue(MyField) Between [start date] and DateAdd("d", 1, CDate([end
date]))


You could use the DateValue function to strip the time off the field in your
table, as in:

WHERE DateValue(MyField) Between [start date] and [end date]

but I wouldn't recommend that, since it'll have to apply the function to
every row.
 
Try (not tested):

WHERE [DateField] BETWEEN [StartDate]
AND ([EndDate] + #23:59:59#)
 
Back
Top