Criteria in SQL/ms-access is only considering day of the month, not full date

Question

NB: this is a follow up question from Syntax of MS Access/SQL sub-query including aggregate functions.

I am trying to produce a database to manage maintenance of equipment. I have two tables:

One (Inventory) containing details of each piece of equipment, including Purchase Date and Service Period, One containing details of work done (WorkDone), including the date the work was carried out (Work Date). I would like a query that displays the date that it should be next serviced. So far I have:

SELECT Max(NZ(DateAdd('m', i.[Service Period], w.[Work Date]),
          DateAdd('m', i.[Service Period], i.[Purchase Date]))
      ) AS NextServiceDate, i.Equipement
FROM Inventory i LEFT JOIN WorkDone w ON i.ID = w.Equipment
GROUP BY i.Equipement

I would now like to order by NextServiceDate and only show entries where NextServiceDate is in the next week. However adding

HAVING (((Max(Nz(DateAdd('m',i.[Service Period],w.[Work Date]),DateAdd('m',i.[Service Period],i.[Purchase Date]))))<DateAdd('ww',1,Date()))
ORDER BY Max(Nz(DateAdd('m',i.[Service Period],w.[Work Date]),DateAdd('m',i.[Service Period],i.[Purchase Date])));

only shows when the day of the month is less than one week from now (e.g. if it is the 1st today it will show all entries where NextServiceDate occurs in the first 7 days of any month of any year, past or future). For some reason it is only considering the day of the month and not the full date. I don't understand why...

NB: I have a British system so date format is dd-mm-yyyy.

score 1 · Accepted Answer · answered Apr 02 '18 at 12:20

1

After a cursory review of your code, I'm unsure whether the instances of i.Equipement is a typo (since your JOIN clause refers to a similar field w.Equipment), or whether these two fields are intentionally named differently?

I can't see anything else immediately wrong with your code, but it may be clearer and easier to debug if you were to restructure the code to the following:

SELECT 
    Max(sub.dat) as NextServiceDate, sub.eqp
FROM
(
    SELECT 
        DateAdd('m',i.[Service Period],Nz(w.[Work Date],i[Purchase Date])) as dat, i.Equipement as eqp
    FROM
        Inventory i LEFT JOIN WorkDone w ON i.ID = w.Equipment
) AS sub
GROUP BY 
    sub.eqp
HAVING 
    Max(sub.dat) < DateAdd('ww',1,Date())
ORDER BY 
    Max(sub.dat)

Note that the difference in regional date formats will only have an effect when you are specifying literal dates (for example, as criteria), in which case you would need to adhere to the format #mm/dd/yyyy#.

answered Apr 02 '18 at 12:20

Lee Mac

15,615
6
32
80

There is a one-many relationship between _i.Equipment_ and _w.Equipment_ as _Inventory_ is a list of all the equipment and _WorkDone_ is the full service history so each piece of equipment may appear in _WorkDone_ many times. – J.Warren Apr 03 '18 at 06:12
Well that's bizarre: other than looking prettier I can't tell the difference between your code and mine, but yours seems to work and mine doesn't... – J.Warren Apr 03 '18 at 06:21
1

@J.Warren My point regarding the various *Equipment* fields was that `i.EquipEment` is spelt with an extra `e`, whereas `w.Equipment` is not. – Lee Mac Apr 03 '18 at 12:18
1

@J.Warren Note that in my code the `Nz` function is evaluated prior to the `DateAdd` function - this may be the source of the difference. – Lee Mac Apr 03 '18 at 12:19

Criteria in SQL/ms-access is only considering day of the month, not full date

1 Answers1