Dates again!!! (1 Viewer)

[Ellie]

I want to calculate the difference in days between two dates which are entered by the user. They can be any two dates - a start date and an end date, but I need to have the result shown in number of days. I have created two fields in a query to hold the user input and then tried using DateDiff("d",[StartDate],[EndDate]) but this gives me some weird results. Any ideas??

 

[Bru]

Ellie
This is a set of queries I used for finding overdue equipment for calibration. The underlying tables held the equipment info, and the calibration table held the various calibration data for the equipment as it was checked.
Query Maxdate is was where the greatest or latest date from the table was sourced.
Maxofdate was where the math took place to figure out when the calibration was due next, by adding an interval to the last date calibrated.
The next queries did math to figure out how many days the equipment had left to go.
Then the total was checked to compare with a predetermined number, in this case, 30 days.

SELECT Max([Repair and Calibration data].Date) AS MaxOfDate, [Calibratable Equipment Query].EQID, [Calibratable Equipment Query].Description, Interval.Intday, [Interval].[Intday]+[Date] AS tot, [Repair and Calibration data].Date, [Calibratable Equipment Query].Make, [Calibratable Equipment Query].Model, [Calibratable Equipment Query].[Serial #]
FROM Interval INNER JOIN ([Repair and Calibration data] INNER JOIN [Calibratable Equipment Query] ON [Repair and Calibration data].EQID = [Calibratable Equipment Query].EQID) ON Interval.Intday = [Calibratable Equipment Query].Intday
WHERE ((([Repair and Calibration data].Calibrateorrepair)="Calibrate"))
GROUP BY [Calibratable Equipment Query].EQID, [Calibratable Equipment Query].Description, Interval.Intday, [Interval].[Intday]+[Date], [Repair and Calibration data].Date, [Calibratable Equipment Query].Make, [Calibratable Equipment Query].Model, [Calibratable Equipment Query].[Serial #]
ORDER BY [Calibratable Equipment Query].EQID;

SELECT Max([Repair and Calibration data].Date) AS MaxOfDate, [Calibratable Equipment Query].EQID, [Calibratable Equipment Query].Description, Interval.Intday, [Interval].[Intday]+[Date] AS tot, [Repair and Calibration data].Date, [Calibratable Equipment Query].Make, [Calibratable Equipment Query].Model, [Calibratable Equipment Query].[Serial #]
FROM Interval INNER JOIN ([Repair and Calibration data] INNER JOIN [Calibratable Equipment Query] ON [Repair and Calibration data].EQID = [Calibratable Equipment Query].EQID) ON Interval.Intday = [Calibratable Equipment Query].Intday
WHERE ((([Repair and Calibration data].Calibrateorrepair)="Calibrate"))
GROUP BY [Calibratable Equipment Query].EQID, [Calibratable Equipment Query].Description, Interval.Intday, [Interval].[Intday]+[Date], [Repair and Calibration data].Date, [Calibratable Equipment Query].Make, [Calibratable Equipment Query].Model, [Calibratable Equipment Query].[Serial #]
ORDER BY [Calibratable Equipment Query].EQID;


SELECT Maxofdate2.MaxOfDate, Maxofdate2.diff, Maxofdate2.EQID, Maxofdate2.Make, Maxofdate2.Model, Maxofdate2.Description, Maxofdate2.[Serial #]
FROM Maxofdate2
WHERE (((Maxofdate2.diff)<30))
GROUP BY Maxofdate2.MaxOfDate, Maxofdate2.diff, Maxofdate2.EQID, Maxofdate2.Make, Maxofdate2.Model, Maxofdate2.Description, Maxofdate2.[Serial #];


Each paragraph is a query, and it looks confusing like this, but it seems to work. Copy and pase the code in SQL view, then look at the grid, disregard the errors cuz you dont have the tables.

Hope this helps
Bru



[This message has been edited by Bru (edited 01-03-2000).]

 

[indyaries]

Hmmmmm.......I know that this question is old, but I was going thru looking for an answer to a query question, and thought I would reply. Perhaps it will help someone else.

In the Query Grid, in a blank column, enter this in the Field Row of the blank column;

Days: [End_Date]-[Start_Date]+1

This will return the elapsed Calendar Days;

Start=01/01/2002 / End=01/10/2002 = 10 Days