Calulating a Time field and adding 15 minutes. 2

[Taznleo]

I have a query that I am using that has the following fields.
Arrival Time, Appointment Time, Start Time, Departure time.
I am trying to write a IIF statement that tells me.
If arrival Time is prior to appointment time or 15 minutes after appointment time, it needs to calculate from Appointment time to Departure time. If Arrival time is over 15 minutes after appointment time it needs to calculate from Start time to Departure time.
Is this too confusing? It is for me, but this is what my end user has requested and I don't know how to write it. I am using MS Access 2000 database.
Thanks,
Taznleo

 

[scriverb]

Give this SQL a try. Update the red code with the name of your table. This code displays the four times as stated in your posting plus a calculated field of the number of minutes as you instructed. Now we can adjust the calculated value to hours and minutes if you need that. Just let me know and we can make those changes. I just wanted to get the IIF statement working correctly

Select A.[Arrival Time], A.[Appointment Time], A.[Start Time], A.[Departure Time], IIF(A.[Arrival_Time] < A.[Appointment Time] and A.[Arrival Time] <= DateAdd(&quot;n&quot;, 15, A.[Appointment Time]), DateDiff(&quot;m&quot;, A.[Appointment Time], A.[Departure Time]), DateDiff(&quot;m&quot;, A.[Start Time], A.[Departure Time])) as Calc_Minutes
FROM tblYourTableName as A;

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[jhall156]

TimeCalc=DateDiff(&quot;n&quot;,DepartTime,IIF(ArrivalTime <= DateAdd(&quot;n&quot;, 15, ApptTime), ApptTime, StartTime)

JHall

 

[Taznleo]

Thanks Guys for the help, by putting both of your answers together I ended up with the result I needed.
This is what I ended up with:
SELECT tblInboundLog.[Trailer#], tblInboundLog.ReceiveDate, tblInboundLog.ArrivalTime, tblInboundLog.StartTime, tblInboundLog.FinishTime, tblInboundLog.Customer, tblInboundLog.Carrier, tblInboundLog.LoadType, tblInboundLog.Comments, tblInboundLog.ApptTime, tblInboundLog.DepartureTime, DateDiff(&quot;n&quot;,IIf([ArrivalTime]<=DateAdd(&quot;n&quot;,15,[ApptTime]),[ApptTime],[StartTime]),[DepartureTime])/60 AS [Elapsed Time]
FROM tblInboundLog
WHERE (((tblInboundLog.ReceiveDate) Between [Forms]![frmRptDialogDate]![StartDate] And [Forms]![frmRptDialogDate]![EndingDate]));

 

[scriverb]

Taznleo: Glad that we could help you. Be aware that by dividing by 60 gives you the whole and fractional hours. If you want it in Hours and Minutes I can provide you with a thread that will convert it for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[fun4me]

Bob,

Can you please provide the thread for hours and minutes using datediff?

Thanks

 

[scriverb]

I have included three threads that all deal with the same subject in different manners. This is always an issue when adding and displaying Time. Sometimes either the programmer or the end user gets confused by the fractional portion of the hours value.

Thread701-636700
Thread701-620206
Thread705-581043

Post back with any further questions that you may have.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

In addition, JudgeHopkins provides a link:

http://www.developershandbook.com/downloads/2162ch03.pdf

That provides some great code for handling Formating Time Differences and Formatting Elapsed Time Differences. This link downloads an 80 page PDF file with many helpful ideas and code instances. It is from a publication by Ken Getz and Mike Gilbert. The sections that you would be interested in would start on page 68 of the PDF file or page 209 of the actual document. You can save the document to your computer and then copy and paste the code to create these functions that can be used to convert the time formats as you need them.

Good luck.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???