Subtracting 2 dates and 2 times from seperate boxes

[aphex]

Jus thought I'd offer this code to the forum as spent a long time finding a suitable solution..

SELECT DischargeTime.ID, [Arrival Times].ID, [Arrival Times].[A&EArrivalDate], [Arrival Times].[A&EArrivalTime], DischargeTime.[Date of outcome], DischargeTime.[Time of outcome], DateDiff("d",[A&EArrivalDate],[Date of outcome]) AS DateDifference, [Time of outcome]-[A&EArrivalTime] AS TimeDifference, IIf([DateDifference]>=1,([DateDifference]*(1/24)*576)+[TimeDifference]*(1/24)*576,([DateDifference]*(1/24)*576)+[TimeDifference]*24) AS hours3
FROM DischargeTime INNER JOIN [Arrival Times] ON DischargeTime.ID = [Arrival Times].ID;

Basically I had an admission date and in a seperate box an admission time and then I had a discharge date and discharge time again in seperate boxes. The information was spread over two tables hence the join. How I solved the problem was by gaining both the days difference between and then the difference in hours and then I created an iff function and played around with it till it worked. Dont really know how i got it working in the end but it does so hopefully this code will save some1 alot of time!

(I know that if id shown my dates as date/time in 1 box the problem would have been much easier solved, however I dont personally like displaying dates and times that way especially when you have inexperienced people entering data)

Find below a result from the query

DischargeTime.ID Arrival Times.ID A&EArrivalDate A&EArrivalTime Date of outcome Time of outcome DateDifference TimeDifference hours3
1 1 19/06/2007 15:04 20/06/2007 12:00 1 03:04 20.93

 

[pbaldy]

I can understand why you'd store the data separately, but I would still have stored them as one field. You can still present them separately to the user. In any case, what you can do now (or could have done) is put them together and then do the math. In other words, this will give you a single date/time field:

StartDateTime: CDate([StartDate] & " " & [StartTime])

do the same with the end date & time and you can simply subtract to get the difference. Just an alternative for you or anyone who may see this thread while searching.

 

[aphex]

Ignore me, do what pbaldy says!