Calculate the time interval using seperate Date and Time fields

[testdba]

Okay, like hundreds of other people around here I'm not an Access guru and I have a problem.

I am trying to calculate the time between two dates that are split between four different fields. The form fields are DownDate, DownTime, UpDate and UpTime. I'm using four fields because I need for the user to put in an exact date and exact time and I don't trust the users enough to put it all in one long general date field.

I have a module that will calculate the date interval, but it requires a start and end date/time in general date format. I have tried to combine the DownDate field with the DownTime field and the UpDate field with the UpTime field, but I get an error when I try to display the calculated result. I know there isn't anything wrong with the function because it works if I use general date fields as the start and end fields. Does anyone have any suggestions?
How can I combine two fields into one general date field or is that even what I need to do?

Thanks in advance for the help.

 

[maxxximaniac]

there is a Datediff function that will do that for you... i dont have it off the top of my head... but you can just hit F1 anc use help..

as for the user putting in data... just use an input mask and that will ensure data can only be entered in one specific method...

 

[Pat Hartman]

Date fields are double precision numbers. You can add the date and time fields and then use the DateDiff() to calculate the difference in whatever interval you want. Or you can download my useful date functions db and use the special date difference function there.

DateDiff("h", (DownDate + DownTime) - (UpDate + UpTime))

Here's a display of some date stuff from the immediate window so you can see how this works:

print cdbl(date())
38337
print cdbl(now())
38337.7006365741
print cdbl(time())
0.70087962962963
print date() + time()
12/16/2004 4:50:02 PM
print cdbl(date() + time())
38337.7051388889

 

[testdba]

Date fields are double precision numbers. You can add the date and time fields and then use the DateDiff() to calculate the difference in whatever interval you want. Or you can download my useful date functions db and use the special date difference function there.:

Where can I download this?

DateDiff("h", (DownDate + DownTime) - (UpDate + UpTime))

To make sure I understand this right, I would use the above line to calculate the difference between the start and end date/time in the place of the function that I am currently trying to use. Right? Would the code be placed in the data field for the text box control?

 

[Mile-O]

You can download it from here.

 

[testdba]

Thanks

Thanks SJ McAbney. I downloaded it and I'm trying to integrate part of it into my App. I'm still learning Access and VBA, so can you tell me if this will work? I am able to add the date and the time as a DOUBLE and when I put it to a text field as general date, the result is correct. Instead of using two text fields, can I not just use two variables that have the values assigned to them? For some reason this is giving me a little trouble.

 

[Pat Hartman]

I don't understand your question but if you want to work with the data as a date, DO NOT format it. Formatting a date turns it into a string. Only some of the date functions will work with dates formatted as strings.

 

[sonny]

Here is a link concerning dates

http://office.microsoft.com/en-us/assistance/HP010984861033.aspx