Calculating Time difference in hours between 4 fields

[Krays23]

Hi

I have kind of made a rod for my own back here with my data base in the table I have 4 fields

Start date
Start time
Finish date
Finish time

I want to find out using a query how many hours there are between the Start date and time and Finish date and time.

Simple enough id imagine with just 2 fields but how do I make a query so it shows the result in hours in just one field?

Appreciate your help as always.

Dan

 

[bob fitz]

What are the data types of the four fields and can you post some sample data

 

[Krays23]

Hey Bob

they are all date/time

Start date = dd/mm/yyyy
Start time = h:nn:ss AM/PM
Finish date = dd/mm/yyyy
Finish time = h:nn:ss AM/PM

The export of the table is attached

Im after a query Bob to show total time difference in hours between the Start data and time and finish date and time

Cheers

 

[bob fitz]

As a calculater field. Something like :
Result: DateDiff("h", [Start Date] + [Start Time], [End Date] + [End Time])

 

[Krays23]

Put that in the critrea field ?

 

[bob fitz]

Put that in the critrea field ?

No. Use it in an empty column (first row) as a calculated field called Result.

 

[bob fitz]

A more accurate solution would be:
Result: Format(DateDiff("s",[SDate]+[STime],[EDate]+[ETime])/3600,"#,###.00") & " Hrs"

 

[Krays23]

HI Bob

Thanks for your efforts however its close but gets some weird results in amongst good ones

-1028109 this comes up a lot? you get like ten good results then this random bad one every now and then

 

[bob fitz]

Can you post a copy of the data you tested with and show us the expression you have used in your query

 

[Krays23]

HI Bob

Attached

seems to work ok to a point then throws up weird numbers

 

[bob fitz]

Sorry. I can't open that type of file at the moment

 

[Krays23]

Hi Bob

What type of file do you need? this forum wont accept it as XLSX

 

[almahmood]

The Start time field has date and time both instead of only time. So, you need to extract only time and apply them into your calculation .

Try this:
Result: Format(DateDiff("s",Int([Start date])+([Start time]-Int([Start time])),Int([Completed date])+([Completed time]-Int([Completed time])))/3600,"#,###.00") & " Hrs"

 

[Krays23]

Gents thanks a lot works perfectly.

I had a few completion times missing also.

Thanks BOB and Mahmood