CODEWITHSUNDEEP
sqlms-access
Leroy
Leroy

Reputation: 644

Access SQL appears to be treating date as dd/mm/yyyy?

I have a table in MS Access which holds staff details (tblStaff):

| Employee Number |  Employee Name |  Dept     |
------------------------------------------------
|     205147      |   Joe Bloggs   |  IT       |
|     205442      |   John Doe     |  Accounts |

I refresh this table with new data weekly and if any records have changed (e.g. changed dept) then they are archived in another table (tblArchiveStaff) along with the dates that that record was valid from and to.

| Employee Number |  Employee Name |  Dept     | DateFrom | DateTo   |
----------------------------------------------------------------------
|     205147      |   Joe Bloggs   |  HR       | 03/01/16 | 01/06/17 |

I am trying to write a query that will select records from either of these tables based on which ones were valid for a given date

We can assume that records in tblStaff are valid from the dateTo + 1 of the last entry for that employee in tblArchiveStaff, or from 03 Jan 16 if they have no archived records.

So far I have come up with the below query into which I have hardcoded the date condition of #07/01/2017#:

SELECT [Employee Number], [Dept], DateFrom, DateTo
FROM 

     (
      SELECT ts.[Employee Number], ts.[Dept], nz(ta2.DateTo,#01/02/16#)+1 AS DateFrom, date() AS DateTo
      FROM tblStaff ts LEFT JOIN tblArchiveStaff ta2 ON ts.[Employee Number] = ta2.[Employee Number]

      UNION ALL

      SELECT ta.[Employee Number], ta.[Dept], ta.DateFrom, ta.DateTo
      FROM tblArchiveStaff ta

     ) AS tblUnion

WHERE #07/01/2017# BETWEEN DateFrom AND DateTo;

As I understand it the above query should return records valid on July 1st 2017 which would be both records in tblStaff, it is however returning the record in tblArchiveStaff. It is almost like it is treating the date condition as 7 January which would mean it is formatted dd/mm/yyyy which I thought was impossible.

Can anyone explain this please?

Upvotes: 2

Views: 938

Answers (2)

Gustav
Gustav

Reputation: 55841

That is because you don't play by the rules. Always handle dates as date values, not strings, not numbers, no exceptions.

So, when you use Nz and even add 1, the union query cannot figure out the datatype, thus it falls back to return the result as text. Then DateFrom becomes Text while DateTo is Date which makes filtering a wild guess.

*Edit - I have also amended the first part of the union query to ensure the current record follows on from the most recent archived record)

Correct as this:

SELECT 
    tblUnion.[Employee Number], 
    tblUnion.[Dept], 
    tblUnion.DateFrom, 
    tblUnion.DateTo
FROM 
    (SELECT 
        ts.[Employee Number], 
        ts.[Dept], 
        DateAdd("d", 1, Nz(ta2.MaxDateTo, #01/02/16#)) AS DateFrom, 
        Date() AS DateTo
    FROM 
        tblStaff ts 
    LEFT JOIN 
       (SELECT 
        [Employee Number], 
        max(DateTo) AS MaxDateTo
    FROM 
        tblArchiveStaff
    GROUP BY 
       [Employee Number]) ta2 
        ON ts.[Employee Number] = ta2.[Employee Number]

    UNION ALL

    SELECT 
        ta.[Employee Number], 
        ta.[Dept], 
        ta.DateFrom, 
        ta.DateTo
    FROM 
        tblArchiveStaff ta) AS tblUnion
WHERE
    #7/1/2017# Between [DateFrom] And [DateTo];

and you will get the desired output:

Employee Number Dept      DateFrom  DateTo
205147          IT        2017-01-07    2017-07-05
205442          Accounts  2016-01-03    2017-07-05

Upvotes: 3

Trevor
Trevor

Reputation: 2922

Standard date flipping errors. Try to always use neutral date formats in your code like

d MMM yyyy

1 Jul 2017 (viable but not recommended because of language differences)

or

yyyy-MM-dd

2017-07-01 (recommended, will work everywhere)

Upvotes: 0

Related Questions