Query Time Difference AND Monthly Totals (1 Viewer)

[gayansam]

Dear CJ_London

Thanks for the information, however 20:00 and 8:00 result was (-12 h) minus that's why I don't know, I need to calculate two shift day and night, day shift 8:00 to 20:00 and night shift 20:00 to 8:00, some time some people worked 24hours, I made it in Excel because I need to make database.

Thanks,
Gayan

You have coded

Round(DateDiff('n',[Out_T]-[In_T],1)/60

The actual structure required is
DateDiff(interval, date1, date2 [, firstdayofweek] [, firstweekofyear] )

So you have set date1=[Out_T]-[In_T]
and date2=1

 

[CJ_London]

if you cross the midnight line then your in/out times need to include the date as well

 

[RainLover]

gayansam

I did ask this question before but never got an answer.

In the Table what Data Type are you using. And please could you post some samples of what is stored in the table.

A screen shot of table in Datasheet would be good to see.

A second screen shot with all formatting removed would also help.

 

[gayansam]

This may database,


thanks

 

[gayansam]

Thank Sir, It's correct I included the date,
Thanks,
Gayansam

if you cross the midnight line then your in/out times need to include the date as well

 

[RainLover]

Can you convert the Database to Version 2003.

I don't have 2007 or later.

 

[gayansam]

Dear RainLover,

It's here

 

[RainLover]

Most Important that you do not use Date as the name of a field. Date is a reserved word. Same as things like Now, Name and If.

Do a Google for a full list which is quite large.

More to come shortly.

 

[RainLover]

I have just taken the first calculated field and made a small adjustment.

     Total_h: DateDiff('n',[In_T],[Out_T])/60

Do a Copy Paste into your query. It should be correct.

You may want more so get this part sorted then post back.

Access Help has some good information on this sort of thing.

 

[gayansam]

Thanks Dear RainLover it's correct

I have just taken the first calculated field and made a small adjustment.

     Total_h: DateDiff('n',[In_T],[Out_T])/60

Do a Copy Paste into your query. It should be correct.

You may want more so get this part sorted then post back.

Access Help has some good information on this sort of thing.

 

[RainLover]

It is nice to be able to help with the correct answer.

Good Luck with the balance of the project.

 

[gayansam]

thank dear..