About Rob Henderson Expertise I can answer most MS Access design questions. I also welcome questions on database design and implementation and VBA programming questions.
I also have expierence in application design for all the Office components (Excel, Outlook, etc).
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..
