Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: how to fetch data between two date parameters Post Reply Post New Topic
Author Message
Padma
Newbie
Newbie
Avatar

Joined: 09 Sep 2010
Location: India
Online Status: Offline
Posts: 20
Quote Padma Replybullet Topic: how to fetch data between two date parameters
    Posted: 25 Jan 2012 at 2:34am
Can someone help to how to write a formula to fetch the data between 2 date parameters in cyrstal report from the database.
 
EG: start_month_year (parameter1) = Jan-2011
      end_month_year (parameter2) = Mar-2011
 
I need the report to show the records which are in present between these dates.
 
Thanks,
Padma
 
priya
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 25 Jan 2012 at 3:09am

create a formula;

SelectionFormula:
if
{table.datefield} >= {?Paramter1}
and
{table.datefield} <= {?Parameter2}
then "Show"
else "Hide"
 
Now goto Report > Select Expert and apply a filter on @SelectionFormula is equal to "Show".
 
Regards,
Ryan.


Edited by rkrowland - 25 Jan 2012 at 3:10am
IP IP Logged
Padma
Newbie
Newbie
Avatar

Joined: 09 Sep 2010
Location: India
Online Status: Offline
Posts: 20
Quote Padma Replybullet Posted: 25 Jan 2012 at 3:59am
Thanks for your help,
 
but since im a newbie to crystal report can you please help where to write this formula and how to apply filter on the selection formula.
 
 
priya
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4368
Quote lockwelle Replybullet Posted: 25 Jan 2012 at 8:30am
rkrowland has pretty much given the solution, create a formula and what to do with it, but...
the bigger question is what does your date look like in your data?
if your datefield is a datefield, and your parameter is a string (which I am guessing it is) you will need to convert one to the other...probably the datefield to a string, but telling if a string is between two other strings is much trickier...so on second thought, convert the parameters to dates...still not fun but something like:
shared datetimevar param1;
local numbervar yr;
local numbervar mth;
local numbervar dash;
local stringvar month;
dash := instr((?parameter1), "-");
month :=left((?parameter1), 3);
if month = "Jan" then mnth:=1
else if month = "Feb" then mnth:=2
else if month = "Mar" then mnth:=3
else if month = "Apr" then mnth:=4
else if month = "May" then mnth:=5
else if month = "Jun" then mnth:=6
else if month = "Jul" then mnth:=7
else if month = "Aug" then mnth:=8
else if month = "Sep" then mnth:=9
else if month = "Oct" then mnth:=10
else if month = "Nov" then mnth:=11
else if month = "Dec" then mnth:=12;
yr:=mid({?parameter1}, dash + 1);
param1 := cdate(yr, mnth, 1);
 
you would do the same for other parameter, though to ensure the last day of the month I would probably do something like
param2 := cdate(yr, mnth, 1);
param2 := dateadd("d", -1, dateadd("m", 1, param2));
 
now you can check that the table date is in the range of parameters, like:
{table.datefield} in param1 to param2;
 
HTH
i know that there is a lot here.  The outline is to convert the parameter to a date value (since it is probably a string value) and then to check if the record in question is in this range.
 
Typically for filtering out records, I will use the Report/Selection Formulas/Record.  the last line {} in {} to {}, is either true or false, and if it is true, then record will be in the recordset, otherwise it will be excluded.
 
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Posted: 17 Feb 2012 at 12:17am
If you have already created your parameters, it will be fairly simple. Go to Report -> Selection formulas -> Records, and type the following code

{table.fieldname} in {?Start date} to {?End date}

Where {?Start date} is de name of your parameter for the start date, and {?End date} is de name of your parameter for the end date.

When you run the report now, you will be asked for your start and end date, and he will only return records between these 2 dates
IP IP Logged
TheSaint
Newbie
Newbie
Avatar

Joined: 30 Aug 2012
Online Status: Offline
Posts: 8
Quote TheSaint Replybullet Posted: 30 Aug 2012 at 9:45am
I know this thread is old, but I want to give it a bump because I just spent 3 days trying to figure this out!
Oops...
IP IP Logged
comatt1
Senior Member
Senior Member
Avatar

Joined: 19 May 2011
Online Status: Offline
Posts: 337
Quote comatt1 Replybullet Posted: 31 Aug 2012 at 5:51am
somewhere in the where clause put "isdate({field)) if the value in database is a string.
IP IP Logged
zanyar-Jalal
Newbie
Newbie
Avatar

Joined: 06 Sep 2012
Location: Iraq
Online Status: Offline
Posts: 12
Quote zanyar-Jalal Replybullet Posted: 12 Sep 2012 at 7:43pm
Dear Padma,
this is the simplest and best way to create a date range parameters:

1)create a parameter for start date.
2)create a parameter for end date.
3)now go to report -> select expert -> record write this code:



{your field name} in {?first date parameter} to {?end date parameter}


Edited by zanyar-Jalal - 12 Sep 2012 at 7:48pm
IP IP Logged
Alexander
Newbie
Newbie


Joined: 10 Jan 2012
Online Status: Offline
Posts: 12
Quote Alexander Replybullet Posted: 28 Oct 2012 at 12:09am
Dear Jalal,
Going thru yr reply. can pls tell me if i dont want to give any date range & i just want to refresh the report from 01st of the month to till date of the month.
 
Regards
Alex
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4368
Quote lockwelle Replybullet Posted: 30 Oct 2012 at 8:47am
you can create variables that mimic that range.
the current date of the month is the Today() function.
 
the first of the month, might have a function, but you can create the date using
Date(year(today), month(today), 1) or something very similar, I am not sure if Date() is the correct function. Check Help.
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.008 seconds.