Search code examples
ms-accessms-access-2010dateadd

Why isn't Access interpreting this time range correctly in an expression?

I am writing an expression for a query in MS Access. The overall goal is to create a new field that acknowledges that just because 3rd shift (10pm-6am) crosses midnight, it should still return the original day that they started. I'm having trouble getting this to work.

Psuedocode:

Date: IIF([RecordDate] has a time between 12:00AM and 6:00AM, return [RecordDate] - 1 day, [RecordDate])

Actual code

Date: IIf((Format([dbo_jobtran]![RecordDate], "Short Time") Between #00:00:00# And #6:00:00#),DateAdd("d",-1,Format([dbo_jobtran]![RecordDate],"Short Date")), [dbo_jobtran]![RecordDate])

Output (incorrect)

RecordDate               Expression
8/10/2015 11:58:09 PM    8/10/2015
8/11/2015 12:07:52 AM    8/11/2015
8/11/2015 5:55:21 AM     8/11/2015
8/11/2015 6:17:06 AM     8/11/2015

Intended Output

RecordDate               Expression
8/10/2015 11:58:09 PM    8/10/2015
8/11/2015 12:07:52 AM    8/10/2015
8/11/2015 5:55:21 AM     8/10/2015
8/11/2015 6:17:06 AM     8/11/2015
Solution

  • Format() returns a string value. You're on shaky ground when trying to compare a string value with a Date/Time value. The comparison requires Access to cast one of those values to the datatype of the other. Sometimes Access gets it right (casts the one you want as you want it cast), but don't depend on that.

    Another way to examine the problem you're facing is to test this query:

    SELECT Format(#8/11/2015 5:55:21#,"Short Time") Between #00:00:00# And #06:00:00# AS Expr2;

    Access returns 0 (False) for Expr2.

    Always use a Date/Time value to compare with another Date/Time value:

    SELECT
    j.RecordDate,
    IIf
        (
            TimeValue(j.RecordDate) Between CDate(0) And CDate(.25),
            DateValue(j.RecordDate) -1,
            DateValue(j.RecordDate)
        ) AS start_date
FROM dbo_jobtran AS j

    Notes:

    1. TimeValue returns the time portion from your Date/Time value and returns that time of day on day 0 (Dec 30 1899). There is no such thing as a time-only datatype, or a day-only datatype, in Access --- we only have Date/Time.
    2. DateValue returns the date from your Date/Time value with midnite (12 AM) as its time of day.
    3. CDate(0) is Dec 30 1899 12 AM
    4. CDate(.25) is Dec 30 1899 6 AM
    5. If you prefer DateAdd, substitute DateAdd('d', -1, DateValue(j.RecordDate)) where I used DateValue(j.RecordDate) -1