Help combining code in query.

[Guest]

I am using the two lines of code below Each in separate columns of a query
(or two text Boxes on a form) to calculate the elapsed time between two dates
formatted as General Dates. The first line of code (in column A, "txtTime1")
calculates to the total minutes between the two dates, the second line of
code (in column B, "txtTime2") takes the minutes and converts them to total
elapsed hours and minutes. They work great, however, I would like to combine
this code and place it in only one query column or form Text Box. My VBA is
not up to it, can someone out there show me how to combine the code below to
produce the hours and minutes in one line of code? Thanks

=DateDiff("n",[StartDate],[CompleteDate])

=[txtTime1]\60 & Format([txtTime1] Mod 60,"\:00")

 

[John Spencer (MVP)]

Just replace txtTime1 with the formula

=DateDiff("n",[StartDate],[CompleteDate])\60 &
Format(DateDiff("n",[StartDate],[CompleteDate]) Mod 60,"\:00")

If StartDate or CompleteDate is ever null (blank) this will probably give you an error.

 

[Guest]

Thanks John, just what I needed. On the Null problem, can I use a NZ()
function?


denny G.

Just replace txtTime1 with the formula

=DateDiff("n",[StartDate],[CompleteDate])\60 &
Format(DateDiff("n",[StartDate],[CompleteDate]) Mod 60,"\:00")

If StartDate or CompleteDate is ever null (blank) this will probably give you an error.

I am using the two lines of code below Each in separate columns of a query
(or two text Boxes on a form) to calculate the elapsed time between two dates
formatted as General Dates. The first line of code (in column A, "txtTime1")
calculates to the total minutes between the two dates, the second line of
code (in column B, "txtTime2") takes the minutes and converts them to total
elapsed hours and minutes. They work great, however, I would like to combine
this code and place it in only one query column or form Text Box. My VBA is
not up to it, can someone out there show me how to combine the code below to
produce the hours and minutes in one line of code? Thanks

=DateDiff("n",[StartDate],[CompleteDate])

=[txtTime1]\60 & Format([txtTime1] Mod 60,"\:00")

 

[John Spencer (MVP)]

You can use NZ on StartDate and CompleteDate. Only you know if that will give
you results you can use.

Thanks John, just what I needed. On the Null problem, can I use a NZ()
function?

denny G.

Just replace txtTime1 with the formula

=DateDiff("n",[StartDate],[CompleteDate])\60 &
Format(DateDiff("n",[StartDate],[CompleteDate]) Mod 60,"\:00")

If StartDate or CompleteDate is ever null (blank) this will probably give you an error.

I am using the two lines of code below Each in separate columns of a query
(or two text Boxes on a form) to calculate the elapsed time between two dates
formatted as General Dates. The first line of code (in column A, "txtTime1")
calculates to the total minutes between the two dates, the second line of
code (in column B, "txtTime2") takes the minutes and converts them to total
elapsed hours and minutes. They work great, however, I would like to combine
this code and place it in only one query column or form Text Box. My VBA is
not up to it, can someone out there show me how to combine the code below to
produce the hours and minutes in one line of code? Thanks

=DateDiff("n",[StartDate],[CompleteDate])

=[txtTime1]\60 & Format([txtTime1] Mod 60,"\:00")