Problem accepting date parameter

[smalley]

I created a report that needs to accept a date parameter. I am using CR8 and the data is in MS Access 97. The data type in Access is Date/Time.

Here is what I did:

First, I created a parameter with the type of string. I called it "DateParam".
Next, I created a formula to convert the string to a date:

DateValue(DTSToDateTime({?DateParam}))

I called this AuditDate. Last, I created a selection formula:

{qryAudit.Date} = {@AuditDate}

When I run the report and supply the date/time (01/02/2001 00:00), no records are returned. I have double checked the data and the date is valid. I have done this before with CR8 and Oracle8i and had no problems. I am connecting the report to a query in Access and not a table. Does this have something to do with it?

 

[JustineB]

Srry if I am wrong, I am not near a machine with Crystal on it, so cannot check if I am correct, but maybe it's worth a try...
In the options box, select the "date/time to date" box, then create the parameter as a Date type selecting the field directly.

Hope it works - sorry if not!

 

[smalley]

I forgot to metion the reason that I am accepting a string and doing the date/time conversion is because I am passing the date into CR from VB. I am trying to get this to work just in the report (without the VB interface) and I can't get my report to accept any date that I enter.

I did try your suggestion JustinB and that doesn't help. Thanks, though.

 

[databaseman]

SOunds like, due to the date coming from Access and not from a table, CR is trying to do an exact match (i.e. 01/02/2001 00:00) and since nothing matches that /exact/ date time, there are no records returned. Try changing the parameter to a small range (from 01/01/2001 00:01 to 01/02/2001 23:59) or (from 01/02/2001 00:01 to 01/02/2001 23:59).

Databaseman Database Man
Defender of Life, Liberty and the pursuit of accurate database reporting! Follow my free tek-tip adventures at:

http://www.hammerman.com/dbman.html

 

[johnism]

You might allso set the param value to string , then do totext(todate (value)). for some stupid reason this has worked before.