Date Query

[jmthompson]

Hello!

I am having a problem that I just cannot seem to get around. I have looked through the other posts and found part of my answer but let me go ahead and explain it all. I have a form that has different fields for Open Date, Open Time, Close Date and Close Time. I am trying to do a query for a report that will show the whole time the record was open. I saw a post on the time feature the said to ([CloseTime]-[OpenTime])*24. This query kinda worked but it did not give me an actually time I moved the decimal over 1 so I would get results like 5.7959489. This worked okay for the time but I need something to take in account the Days it was open and also with the days being opend the time as well so If I opened it on 10/24/02 at 1:45pm and Closed it on 10/26/02 at 1:50. My result would only equal 5 mins. I need it to take consideration the days and time. Thank you for any help and sorry so long.

jmthompson

 

[zerosandones]

The DateDiff function will solve your problem as follows, calculating the time the record was open in minutes:

DateDiff("n",[OpenDate] & " " & [OpenTime],[CloseDate] & " " & [CloseTime])


Hope this helps.


00001111s

 

[jmthompson]

Thank you for your reply.

Ok now I am lost I have tried the DateDiff everyway possible I think and I am just not getting it. I am a access novice. I understand the datediff and the "N" but where do I need to go to get this to run properly. I am sorry I am such novice.

 

[zerosandones]

You mentioned that you were using a report which, unless you want a full table dump, will have a query as its record source.

Let's use this example for our table named "tblTask" in which we have tasks which have an open and close date and time:

TaskDescription Opendate OpenTime CloseDate CloseTime
Task 1 11/04/0 23:00 PM 11/04/02 4:30 PM
Task 2 11/03/02 10:00 AM 11/04/02 12:30 PM

To generate a list of these tasks including a calculated duration for each task, the following query will do just fine:

SELECT TaskDescription, Opendate, OpenTime, CloseDate, CloseTime, DateDiff("n",[OpenDate] & " " & [OpenTime],[CloseDate] & " " & [CloseTime]) AS [Record Open (min)]
FROM tblTask;

Save this SQL as a query e.g. qryTask then make this query the recordsource for your report. All fields, including the calculated task duration will be available for display within your report.

Hope this helps.

00001111s

 

[lhite]

You can do it a bit more simply by just creating a textbox on the report and for the data source type:
([CloseDate]+[CloseTime])-([OpenDate]+[OpenTime])
Be sure to type the parentheses properly or invert the second addition operation to subtraction. The only other thing you might have to do is play with the format of the result to get it to display the way you want.

Larry