DateDiff question

[IT4EVR]

I'm simply trying to Sum the difference between two smalldatetime fields with a format of medium time. This is a calculated field on the form, NOT in the query. I'm getting an #Error on this one.

=Sum(DateDiff("h",[Time_In],[Time_Out]))

In a view, I'm able to do this and it works:

Sum(DateDiff(hour,[Time_In],[Time_Out]))

How can I get this to work on the form?

 

[genomon]

I'm not clear on what you are trying to do: " Sum the difference "? DateDiff returns an integer, DateAdd returns a date, and it is possible to DateAdd negative numbers (which has the net effect of subtraction).

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[IT4EVR]

This is a time entry form, with fields like Work_Date, Time_In, Time_Out. This is a continuous form for all work dates for a given pay period.

In an Access Project, I have a subform, which serves as this continuous form. I want to create a sum of all the hours worked for that pay period. Normally what I do is create a text box in the header or footer, and create a function to calculate the Sum of all hours worked.

I tried Sum([Time_Out]-[Time_In]) with an error.
I tried Sum(DateDiff("h",[Time_In],[Time_Out])) with an error.

I can do this in Access but not in Sql Server.

 

[NthnLShltz]

If this code is giving you an error with the code like this
=Sum(DateDiff("h",[Time_In],[Time_Out]))
In SQL Server 2005 I am able to take the quotes out around the h and it will work. The code looks like this below
=Sum(DateDiff(h,[Time_In],[Time_Out]))
I know that when I was trying to get days what I had to do was a code like this 'DateDiff(h,GetDate())-'and then after the subtraction symbol the time that the date would be coming up. I am not sure if this answered anything for you, but it sounds like the problem I had, and this cleared things up for me.

 

[2Grandpa]

In the past, I have created a hidden text box, mytxtbox, in each line of data on the form with the calculation I want. In your case it would be =DateDiff("h",[Time_In],[Time_Out])). Then the formula in the text box in the header or footer would be =sum([mytxtbox])

 

[IT4EVR]

Thanks for your reply. Even when I did that I got an error. I am actually in the process of porting this app to ASP.NET so I will no longer be using the ADP. I was more or less just using it as a template.

I found ADP's to be very buggy and inefficient. No wonder Microsoft is scrapping them.

 

[Pampers]

I would solve this problem different. If would use an hidden unbound textbox (say calcDifference) on every row with this calculation:
DateDiff("h",[Time_In],[Time_Out])

then in the form footer: Sum([calcDifference])



Pampers
Just back from holiday...
that may it explain it.

 

[cmmrfrds]

Where are the fields
[Time_In] and [Time_Out] defined.

Are these names of textboxes on the continuous form?

 

[Pampers]

yes

This is a calculated field on the form, NOT in the query.

Pampers
Keeping it simple can be very difficult

 

[IT4EVR]

Time_In and Time_Out are defined as bound fields in a continuous form. I am able to create a calculated field (txtDailyHrs) in the form (not query) for each row to get the total hours worked for that day. However, if I create a text box in the footer or header of the form and set the control source to =SUM([txtDailyHrs])I get a #Error in the text box. I've done the same thing in Access and it works.

The error seems to be regarding doing an aggregate function SUM on a date field.