Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: DateDiff question Post Reply Post New Topic
Page  of 2 Next >>
Author Message
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Topic: DateDiff question
     Posted: 28 Sep 2010 at 5:09am
Hello everyone
 
I am a newbe to Crystal Reports XI
 
Never used it before and i am doing farly well.
 
I want to find out the date difference in days between
 
a datetime column (via sql server that is already setup.)
 
And the Currentdate function.  But want results over 50 days.
 
SO far i have a formula field as follows.
 
DateDiff ("d",{Command.INVDATE} , CurrentDate)
 
Could someone help me out please
 
Kind Regards
 
Rob
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Sep 2010 at 5:42am
you mean you want to only select records where it is more than 50 days difference?
In the select expert use your formula with the 50 criteria:
DateDiff ("d",{Command.INVDATE} , CurrentDate)>50
IP IP Logged
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Posted: 28 Sep 2010 at 5:54am
Have tried that but all i get in preview of report is a boolean value of
 
true or false.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 28 Sep 2010 at 6:19am

Do not create a formula field. Formula fields are (primarily) used to convert data for display or cculations.

The select expert is used to filter dat based on a boolean ananlysis of each row of data (TRUE keep, FALSE omit).
Hence why you get a boolean put into your dispaly on the report.
The Select Expert can be found by click on icon of the hand grabbing on red ball.
once you click on it click OK
then click ont he Show Formula
then click on the Formula editor
now but your boolean statement here
DateDiff ("d",{Command.INVDATE} , CurrentDate)>50
whnever a row is true it will staty in the report, flase gets kicked out
(kind of like the WHERE clause in a SQL statement)
 
 
IP IP Logged
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Posted: 28 Sep 2010 at 10:07pm
I have exactly that, but i am only receiving one result when i know  i should be getting 4.
IP IP Logged
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Posted: 29 Sep 2010 at 12:32am
WIth in the formula, i want to print the result from the following
 
DateDiff ("d",{Command.INVDATE} , CurrentDate)
 
Is there a way i can just have this as a figure at.  Can i use The print as at all or something simular.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Sep 2010 at 4:03am
go into the field explorer
right click on formula field and select new
name it
add the formula
DateDiff ("d",{Command.INVDATE} , CurrentDate)
save it.
drag gthe fomrula field onto your detail row and you will see the numeric value of days from the formula
IP IP Logged
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Posted: 30 Sep 2010 at 12:52am
Ok thank you for that, Now i have another little question.
 
I have the following syntax
 
DateDiff ("d",{Command.INVDATE} , CurrentDate) -30,
if {@Days_Due} <= 30 then {@Days_Due} "Current"
 
The top line works ok, but i want to incorporate an IF statement.
 
Could someone help me with the syntax in the second line please.
IP IP Logged
Emir_W
Senior Member
Senior Member
Avatar

Joined: 25 Apr 2010
Online Status: Offline
Posts: 228
Quote Emir_W Replybullet Posted: 30 Sep 2010 at 2:58am
assume:
@Days_Due=DateDiff ("d",{Command.INVDATE} , CurrentDate) -30
 
the conditions will be:
if {@Days_Due} <= 30 then
       "Current"
else
       '..others..'
 
 
 
hope it help.
 
 
 
Emir W
IP IP Logged
masterdineen
Newbie
Newbie
Avatar

Joined: 28 Sep 2010
Location: United Kingdom
Online Status: Offline
Posts: 26
Quote masterdineen Replybullet Posted: 30 Sep 2010 at 3:16am
Thank you very much for that.
I am getting the desired results. My Days_Due are showing in 2 decimal places for eg  43.00
 
How do i change to no decimal places.
IP IP Logged
Page  of 2 Next >>
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