Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: DateDiff Post Reply Post New Topic
Author Message
MrCastalum
Newbie
Newbie
Avatar

Joined: 11 Mar 2010
Location: United Kingdom
Online Status: Offline
Posts: 22
Quote MrCastalum Replybullet Topic: DateDiff
    Posted: 30 Apr 2010 at 1:58am
I am usin Crystal Reports XI.

A little back story:
Basically, we have an SQL server which I have connected to using Crystal Reports. The SQL server is linked to our time and attendance system so basically it logs people's clock in and clock out times. Now,  I have group the records by the date (daily), then by user id, and have calculated the maximum and minimum of user id clock in times for that day (to eradicate the problem of people clocking in and out multiple times for lunch breaks etc.) Now I need to calculate the difference between those maximum and minimum times so I know the amount of time they spent here, minus half an hour lunch break. I believe that I would to this using a formula and the operator 'DateDiff'.

I actually want my formula to be this:
DateDiff (n, Minimum ({Employee.DateTime}, {Employee.UserId}), Maximum ({Employee.DateTime}, {Employee.UserId}))

But I get the error message "the ) is missing" and the 'n' is highlighted.

It gives me the same message if I just try:
DateDiff (n, 29/04/2010 07:48:42, 29/04/2010 16:38:43)

Any idea/suggestions?


Edited by MrCastalum - 30 Apr 2010 at 2:14am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 30 Apr 2010 at 3:14am
put the n in quotes like "n"
 
SQL isn't fussy, but CR is.
 
HTH
IP IP Logged
MrCastalum
Newbie
Newbie
Avatar

Joined: 11 Mar 2010
Location: United Kingdom
Online Status: Offline
Posts: 22
Quote MrCastalum Replybullet Posted: 30 Apr 2010 at 3:30am
Fantastic! Two thumbs up!Smile

IP IP Logged
MrCastalum
Newbie
Newbie
Avatar

Joined: 11 Mar 2010
Location: United Kingdom
Online Status: Offline
Posts: 22
Quote MrCastalum Replybullet Posted: 30 Apr 2010 at 3:36am
One slight problem, I only want to see records when they've worked more than a certain amount of time but the select expert isn't showing any of my formulas?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Apr 2010 at 4:00am

Formulas that use a function that occurs after teh data is returned cannot be used to limit the selection.

You can use a Summarized field that was created with the insert summary function and then in the group portion of the select expert.
You likely will have to try and suppress rather than exclude.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.