Another...Date in Range Quest.

[sbianco]

- MS-Access (DB) table fields:
table.recordId,table.date1,table.name, payperiod, ...
- Using: Visual Studio .Net - C# (OLE DB)
- table.date1 is a "range value"


1. What formula (code) do I use to get only the dates (DB records) in my report for the range I specify in the Crystal Reports (CR) prompting range window

2. What DB data type (string, Date Time) should date1 be? CR value type? Both should match? Can they be strings?

I assume I add this formula to the table.date1 parameter field (?date1) (select expert) I dragged and droped on to my CrystalReportl.rpt

Any further comments appreciated, please don't assume I know anything and your response will be read many times

Thanks
Steve Newbie

 

[synapsevampire]

1. What formula (code) do I use to get only the dates (DB records) in my report for the range I specify in the Crystal Reports (CR) prompting range window

Create a date range parameter (in example as {?DateRangeParm}):

Reference it in the record selection formula as in:

{table.datefield} = {?DateRangeParm}

2. What DB data type (string, Date Time) should date1 be? CR value type? Both should match? Can they be strings?

If you are passing these values from code (which goes against what your post suggests earlier), rather than using the record selection formula and parms in CR, then I would send them as strings and use {table.datefield} = "#3/14/2003#" where the date is passed in string format.

-k

 

[sbianco]

I'd like to thank You for your reply it sovled my problem, I think, however I'd like to give some observations

The procedure I followed was to drag and drop the table.datefield on to the crystalreport1.rpt, create a
DateRangeParm and create a formula for the datefield:
{table.datefield} = {?DateRangeParm}. The DateRangeParm is a String of Range value

The following are FORMAT values I placed into the CR prompting range window. There should be 5 DB records that show in the report for the range specified. The
table.datefield INSIDE MS-Access is of type "Text" and it's format is mm/dd/yyyy

Entering a DateRangeParm with NO default values
02/22/03 & 02/26/03 - only 4 records, 2/26 missing
2/22/03 & 2/26/03 - no records show
2/22/2003 & 2/26/2003 - no records show
(Note: include value is checked)


Entering a DateRangeParm with "Set default values" from table.datefield.
02/22/03 & 02/26/03 - I get all 5 DB records show

If I change the DateRangeParm to Value Type to "Date", I don't get any records in my report but a get a wonderful calendar from which to chose and select from


Your comments?
Can I somehow get the the best for both worlds, e.g. the calendar and all 5 records?

Steve

 

[synapsevampire]

Sure, convert the string date in your database to a more usable date format.

create a sql expression (meaning use an odbc connection) containing:

CDate({table.field})

and use a date type parameter to compare against.

You made need to adjust the cdate in Access, it's been a while since I used Access.

-k

 

[sbianco]

Thanks k

I wish I could say I understood your last reply but I regret I don't. I understand about changing the DB and I'm familiar with SQL statements but what I don't understand is your reference to

"CDate({table.field})
and use a date type parameter to compare against"

Is CDate({table.field}) a method (API) or construct within the SQL statement? Any additional info/explaination would be helpful.

Maybe it's time to hit the bookstore. Thanks

Steve