Date Field and Parameter error 1

[Wayne79]

Using CR8.5

I have a date field called Plan_Departure.
I created a parameter called Dates as a date field, range values.

When I try to create a record selection criteria linking the parameter to the field I get a "String required here." error. The criteria I'm adding is {TRIP.PLAN_DEPART} = {?Dates}

I've verified the database to no avail.
I've added a table and verified the databse, no luck.
I've added fields to the report and verified the databse. Same error.
I've tried to Set Location with no luck.

How do I force CR to verify the database to see that the field actually is a date?

 

[dgillz]

Most likely your database field is a string, and you must first convert it to a date.

To check this, while writing the formula rt click your database field and select browse data, then paste the data into the formula. If it has quotes around it, its a string.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Wayne79]

It did have quotes around it.

I do not have access to the database itself to verify the data type. It is DB2 and I have zero experience with db2. I do have the create table statement in a help file. No garuntee that the table hasn't been modified. The help file says it is a date field.

I wrapped the field the CDate function.

I can save the formula but when I run the report I now get a "Bad date format sting." error. I'm using CDate({TRIP.PLAN_DEPART}) = {?Dates} as my formula.

I've had this problem in the past. Got it fixed...I just don't recall how I did it. Field was a date and somehow a verify database fixed it.

 

[chelseatech]

Inside CR, right click on the field and select "Browse Data" to see what format the date is.

You might need a more complex formula to convert the field to a date. Blank or empty values could also be causeing you problems.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[synapsevampire]

you can create a date out of any string by using the cdate function and passing through either the proper date string format, or convert to values.

Here's an example of a date string converted:

"01-30-2005"

cdate(val(mid({table.date}),7,4),val(mid({table.date},4,2)),val(mid({table.date},1,2)))

Note how the cdate with commas expects values, and in the year, month, day order.

The MID function uses the format of mid({table.field},starting charcter within the string, number of charcters from that point)

Adjust this to suit your date format.

-k

 

[dgillz]

Wayne,

a little tip -- there is an icon in the upper right hand corner of the field explorer window, which is not normally clicked. Click it, and crystal will show the size and datatype of all the fields.

If you still have problems converting this to a real date, post some example data.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Wayne79]

Got it to work. Thanks guys/gals

Had to swith tables (got same error) to get a valid departure date.

Here is the formula I used. The commented out line is a pasted value from the table.

//"2004-09-20-00.00.00.000000"
iif({?Status} = "*", true, {TLORDER.CURRENT_STATUS} = {?Status}) AND
cdate(val(mid({LEGSUM.LS_PLANNED_DEPARTURE},1,4)),val(mid({LEGSUM.LS_PLANNED_DEPARTURE},6,2)),val(mid({LEGSUM.LS_PLANNED_DEPARTURE},9,2))) = {?Dates}