Expert: Rob Henderson - 2/16/2005

Question
-------------------------
Followup To
Question -
-------------------------
Followup To
Question -
I would like to determine the variance of time between entries in a database . The date field is populated every time an entry is made. I would like to use this date field to determine that variance.  I familiar with Access....Thanks
Answer -
Hi

You could use the DateDiff() built in function.

For example,

MsgBox DateDiff("d", Now(), Now() + 7)

This statement would display a message box with the difference in days between Now() and Now()+7

If you substitute the Now constants with your values you'll get what you need.

Note: If you use VBA to declare dates remember that the format is always in American format (mm/dd/yy) regardless of your local settings.

Look up help on the DateDiff() functionality

HTH

rob

Rob, Thanks, this works when you compare two different fields but....What I am after is the Diff between dates in the same field..meaning if I have a list of dates I want to see the difference between each date as it relates to the next date.   ex. 1/1/2005   1
         1/2/2005   3
         1/5/2005   0  etc. Thanks for youur help



Answer -
Hi

Do you mean calculate differences between the field on this record and then the same field on the next and so on?

Yes....In this case I am following work orders for a job.  Each time the status of that work order changes a date is entered in the date field.  SO in the QBE I can see...  Per [workorder] each [Status] change and the [Date_Time] of that status change. If I can calculate the difference between status change the I can tell how long it stayed in each status or what status held up the work order.  For instance if it 'opened' on date(1) Then went to the 'shop' on date(1) then the difference would be 0 days in the opened status.  If it went to 'Supply' for parts on date(5) the difference would be 4 days in shop status. If it then went to 'Shop' on date(15) the difference would then be 10 days in Supply status and finally 'Closed' Date(16) It would have been in shop for 1 Day.  A total of 15 days to complete.   DateDiff("d", Date_Time, Date_Time) produces 0 because it is comparing the same date.  I need to be able to compare the next date..  

Answer
Hi

How is this designed? In a form? a query?

I have the following link which may help

http://support.microsoft.com/?kbid=210504

If you use the above technique and the DateDiff function I'm sure you could implement quite easily.

Give me a shout if you need a hand putting the code together (and also remember to let me know how this is designed).

HTH

rob