crystal report date range

[WalfdPD]

Set a date range crystal reports

After selecting the fields to go into the report place the cursor on the report and
Right click the mouse. Select REPORTS then select REPORT OPTIONS
In report options in the CONVERT DATE-TIME FIELD box choose TO DATE.

Now create a parameter for startdate name it what you will and enter some text to prompt the user.
For the value type choose DATE, then choose discreet values
In the set default value box set the default date to a date earlier
Than the earliest date in the table.

Now create a parameter for enddate name it what you will and enter some text to prompt the user.
For the value type choose DATE, then choose discreet values
In the set default value box set the default date to a date perhaps
10 or 15 years in the future.

Now go to the select expert ,choose the date field you are working with
And select GREATER TO OR EQUAL TO and then in the dropdown
Box choose (?startdate) or whatver it is that you named the parameter.

Now right click on the report again choose report then choose edit selection formula
And then choose records.

The formula should look like this

{TABLE.date} >= {?start}

Edit it to look like this:
{TABLE.date} >= {?start} and

{TABLE.date} <= {?end}
Good luck it should work, mine does

 

[rosemaryl]

Dear Walfdpd,

This is unnecessary. You do not have to change the report options for datetime fields to date to have a paramter as date. I always believe in working with data the way it exists in the database. If you change the report options for DateTime fields to Date, then within your report you wont be able to do any calculations that include time.

This works quite well:

Set your report Convert Date Time Field box back to datetime.

Create your two parameters: Start and End and choose type of Date. Yours are already correct on the existing report.

You can then reference them in the selection criteria like this:

{Table.Date} in {?Start} to {?End)

Your report runner will be prompted with date fields and Crystal will correctly calculate the the records to select. Below is the where clause from the Database/Show SQL Query:

WHERE ("Incident"."Open Date & Time">={ts '2005-04-01 00:00:00'} AND "Incident"."Open Date & Time"<{ts '2005-04-29 00:00:00'})

The start date I selected was 4/1/2005 and the end date was 4/28/2005.

See how it states >= '2005-04-01 00:00:00'
and <= '2005-04-29 00:00:00'

This is correct. It is not so much that your way is wrong, it is just not necessary to get Date parameters to work.

Best Regards,

ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762