Print Page | Close Window

how to fetch data between two date parameters

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15445
Printed Date: 19 Apr 2025 at 12:57pm


Topic: how to fetch data between two date parameters
Posted By: Padma
Subject: how to fetch data between two date parameters
Date 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


Replies:
Posted By: rkrowland
Date 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.

Posted By: Padma
Date 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

Posted By: lockwelle
Date 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.
 

Posted By: Gurbs
Date 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

Posted By: TheSaint
Date 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...

Posted By: comatt1
Date Posted: 31 Aug 2012 at 5:51am
somewhere in the where clause put "isdate({field)) if the value in database is a string.

Posted By: zanyar-Jalal
Date 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}

Posted By: Alexander
Date 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

Posted By: lockwelle
Date 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.


Print Page | Close Window