How query data using date part of a date/time field

In a table I have a date/time field that looks like this for example:

4/16/2014 12:43:41 PM

But I want to query records using just the date part of that information.

My query criteria looks like:

Between [Forms]![frmSelectDatesForLaborTranxReport]![txtStart] And [Forms]![frmSelectDatesForLaborTranxReport]![txtEnd]

But I 'm sure I have to change it somehow to return the records that have 4/16/2014 or whatever date range is entered in frmSelectDatesForLaborTranxReport

How can I do this?

--Steve
SteveL13Asked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Connect With a Mentor Infotrakker SoftwareCommented:
You can use DateValue:

WHERE DateValue(YourDateField) BETWEEN [Forms]![frmSelectDatesForLaborTranxReport]![txtStart] And [Forms]![frmSelectDatesForLaborTranxReport]![txtEnd]
0
 
Dale FyeCommented:
No points please.  I agree with Scott on use of the DateValue ( ), but I don't like using BETWEEN with date fields.  Personally, I would use:

WHERE DateValue([YourDateField]) >= Forms![frmSelectDatesForLaborTranxReport]!txtStart]
AND DateValue([YourDate]) <= Forms![frmSelectDatesForLaborTranxReport]!txtEnd]

Actually, I generally use:

WHERE [YourDateField] >= Forms![frmSelectDatesForLaborTranxReport]!txtStart]
AND [YourDate] < DateAdd("d", 1, Forms![frmSelectDatesForLaborTranxReport]!txtEnd])
0
All Courses

From novice to tech pro — start learning today.