Date Calculations

[Sgt Bilkp]

Evening,

Having a spot of bother with some date calculations. I have two date options (Date_down and Date_returned) for kit that goes away for repair. Not that it probably matters, but these dates are contained in a sub-form for each item of kit (one kit can have many faults).

To keep a track on how reliable the piece of kit is, i would like to work out the days in between, and have this value stored in a field (Daysdown) preferably.

To now, the days have just been shown in a unbound text box using

=DateDiff("d",[Date_down],[Date_returned])

Any suggestions?

 

[RuralGuy]

To be honest, I don't really understand some of the terms you are using but storing a calculated value is usually not a good idea. Just calculate it in a query every time you need it.

 

[missinglinq]

I'm sorry, but what is your question? The expression you have is correct for the situation given. This kind of calculated value doesn't need to be stored, simply re-calculated when needed. This simple a calculation is always faster than a retrieval from disk.

 

[Sgt Bilkp]

OK. Fair points.

Different question, and I probably should have put this in the OP.

I need to add up the total number of lost days due to equipment failure over the last 12 months.

For example, i will have a range of dates as below:

1. 1/5/07 - 1/6/07 (days lost = 31)
2. 1/6/07 - 14/10/07 (days lost = 135 [or 71 if only count within 12 months)
3. 14/5/08 - 20/7/08 (days lost = 67)
4. 1/6/08 - (days lost = 63 to date)
5. 12/6/08 - 12/7/08 (days lost = 30)
6. 1/7/08 - (days lost = 33 to date)

Todays date is the 3/8/08 (obviously).
The first entry started and closed <12 months ago so is not required
The second entry started <12 months ago, but closed in the current period
Entries 3 & 5 are also current. Entries 4 & 6 are current, but have not yet been "closed" (or fixed) and thus the total days "down" can only be claculated "to date". All these dates are listed on the "frmFaults" and are "Date_down", as in when the machine fails, and "Date_returned" as in when the machine is repaired.

Based on this, how can i calculate total days lost? It should show 264.

I accept that it doesn't need to be a stored value. My thinking was flawed originally.

Cheers

 

[RuralGuy]

I would think at the very least you could calculate the days lost in each record in one query and use another query to Sum the first query.

 

[Sgt Bilkp]

Queries the best way to go on this one then?

 

[Brianwarnock]

Queries are fine but I think that you are going to have to write a function to do the calc.
Consider what you need to check

Is the closed date > 12 months ago - ignore
is the closed date null - substitute checking date
is the start date > 12 months ago -substitute 12 months ago date

Points 2 and 3 might be combined

Then as datediff is so poor you will need to work in days and thus allow for leap years being spanned, can't just check the year a s this leap year is spanned until 1st March 2009 .

Of course I could have lost the plot.

Brian

 

[Sgt Bilkp]

Queries are fine but I think that you are going to have to write a function to do the calc.
Consider what you need to check

Is the closed date > 12 months ago - ignore
is the closed date null - substitute checking date
is the start date > 12 months ago -substitute 12 months ago date

Points 2 and 3 might be combined

Then as datediff is so poor you will need to work in days and thus allow for leap years being spanned, can't just check the year a s this leap year is spanned until 1st March 2009 .

Of course I could have lost the plot.

Brian

Pretty much spot on with ref to the checking rules. Didn't think about leap years though!

 

[Brianwarnock]

Didn't think about leap years though!

I suppose you could just hard code this and the next couple in and fire off a warning if the function runs after 1st jan 2020

Brian

 

[Sgt Bilkp]

I suppose you could just hard code this and the next couple in and fire off a warning if the function runs after 1st jan 2020

Brian

Doubt it will be used that far. Probably for another 18 months or so, plus entry input for the last 12 months historical data.

 

[Sgt Bilkp]

After thinking i may not need this one any more, i have been proven wrong by our supplier so i need to rehash this. After a bit of guidance and some research i think i can make it much simpler.

Two date fields DateDown and DateReturned. Is it possible to get an on update event in the DateReturned field to work out the number of days in between and complete a third field, DaysDown?

 

[Brianwarnock]

You are talking about storing a calculated field again, I wouldn't open that can of worms. Just calculate the days down when you need it.

Brian

 

[The_Doc_Man]

If the two fields are actually Date (data type), then they are "casts" of type DOUBLE mapped onto an arbitrary timeline for which the units are days. The Datepart and other time-based formatters use a mapping algorithm to compute the date/time based on that arbitrary timeline. In effect, a Date data type represents the days and fractions of a day since a reference date. I believe (and don't bother to trust this) it is something like, for Access, time zero is 31-Dec-1899. I.e. day 1 is 1-Jan-1900.

Therefore, if you take the LONG() of each date (which truncates the dates to midnight) and subtract them one from the other, you get exact integer days regardless of leap year considerations. Or you can subtract them first as DOUBLE and THEN take the LONG, which could come out different based on the time-of-day differences.

I concur with the others that there is no time you want to store the raw time difference in your table. However, there is nothing wrong with a query that computes the time and a layered query based on the first query to compute the sums. Remember, a table and a query BOTH provide recordsets. A query must be based on a recordset. There is no requirement for all queries to be directly based on tables. Just as forms and reports can be based on queries.

The last wrinkle is what to do for cases where the Time Out is non-blank but the Time Returned is not a date. You need to set up a default for this, then make the time-difference query FILTER OUT the "no Time Returned" cases. Yet another reason why you should consider doing this via queries and make the filtering logic invisible to your report.

 

[Sgt Bilkp]

Right, i see you point now. Since the calculated value is much more than just two or three digits representing the days in between, it is much easier to have a formula work the figure out and enter the result manually in the field.

Is it then possible to have a message box pop up with the figure (of days in between the two dates) on a on update event after DateReturned is completed? That would allow the user to OK the message box and enter the figure?

 

[Sgt Bilkp]

Sorry, since my last post was another question, is it better to keep in this running thread or start a new one?

 

[Brianwarnock]

May I respectfully suggest that you go back and read posts #2 and 3.

Brian

 

[Sgt Bilkp]

I did Brian, but since my original post I changed what i needed to do on advice from people who posted. I now don't need to store a calculated field, i just want a message box to appear displaying the difference between two dates after the end date has been entered. The user can then enter the number of days in a bound field which can be used later for other purposes.

I have done calculations before that show the difference between two dates, but it looks untidy having the calculated field displayed, then a bound text box also showing the same amount (as entered by the user). I do need to store "days lost" so i can work out productivity figures at the end of each year.

My latter question, (post 14?) I thought was a much easier and do-able option? Or is that i am still not making it clear?

Regards

 

[Brianwarnock]

The problem with storing a calculated field is the trouble you have to go to to maintain data integrity, people can go back and change the data, start and end dates, and therefore at any point of access to that data you have to take action. It is simpler and safer to do the calculation when needed, yes even at the end of the year.

Brian

 

[Sgt Bilkp]

The problem with storing a calculated field is the trouble you have to go to to maintain data integrity, people can go back and change the data, start and end dates, and therefore at any point of access to that data you have to take action. It is simpler and safer to do the calculation when needed, yes even at the end of the year.

Brian

Data integrity is not a problem here. Once the fault has been raised and closed, the data cannot be changed and neither do we want the data to be changed. Even if the data is changed at a later date, the original value of days in between is the one that is important since that is what we will have been refunded for by the manufacturers. Any discrepancies (eg the same fault occurs again the next day after fix) would be raised as a new call, with a new ref which incurs a separate charge.

I do understand what you mean, but for this purpose what i am looking to do fits best in this application. It might not make sense from a developers perspective, but from the end user it does. If that makes sense?

All i need is the code to add into after update event. I know i can work out the days difference by using

=DateDiff("d",[DateDown],[DateReturned])

and how to get a message box to appear, but i cannot twin the two.

 

[Rabbie]

Try this

Dim DteDiff as integer
dteDiff = DateDiff("d",[DateDown],[DateReturned])
MsgBox( The date difference is " & DteDiff)