Parameter Field does not show in the list

[Former Member]

Hi,

I am new to Crystal report but not the Database. I tried to follow the TUT step by step and after creating Parameter Field "DateRange" -> Select Expert -> is equal to -> the {?DateRange} does not list in the fields. please may you advise what is the wrong

[daroy_alvin · Accepted solution]

Hi,

Are you using SQL Queries for your crystal report? if yes then try to convert the field into date datatype.

Try this:

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

Thanks & Regards

Alvin

[Former Member · Accepted solution]

No, I am using Data Expert which created Database fields in the report.

Do you know where this query is created in CR so I can modify it?

I can not believe that I can not move forward in this project because of this "Type Mismatch"

Can you tell if this is a bug in CR?

Otherwise, why the records types would be converted

In Visual Studio, DataSet must be match the DB. The nice thing is you can modify the fields types in the Dataset.

Any one know, when CR create the report fields where the query or the data set is located so can modify it

[daroy_alvin · Accepted solution]

If you are not using queries on your report. then try to add this on your select expert.

({DailyReport.ReportDate} >= cDate(ToText(cDate({?DateRange}),"dd/MM/yyyy"))) AND {DailyReport.ReportDate} <= cDate(ToText(cDate({?DateRange}),"dd/MM/yyyy")))) 

Thanks & Regards

Alvin

[Former Member · Accepted solution]

Alvin thanks and your reply is appreciated.

1- I tried your solution and I got error indicated {?DateRange} must be DateTime. So, I changed it to DateTime type from the Parameter Editor. However, still getting same error message "help!!!"

2-  I flipped your equation as follow

((cDate({DailyReport.ReportDate})) >=  {?DateRange}) AND ((cDate({DailyReportDate}) <= {?DateRange})

This approach is running and I think it is working properly. "cross fingers because I am not really sure the mechanism of comparison in CR"

However, because I am not the expert in CR, I would like to see what do you think about this formula where

Report Date in MSSQL is type "DATE" and data is saved as "yyyy-mm-dd" format

While in CR fields, I learned , is saved as "STRING" with Length 10 and "yyyy-mm-dd" format too. (I do not know why CR flip it to String??)

which is converted to type "DATE" but now the format is "mmm dd yy"

The question now with the formula in number 2 is,

Do I need to change the format of ReportDate or even for DateRange ? Because cDate returning  a date of an input string to "Dec. 31 14" and the stored date data is in "yyyy-mm-dd." For comparison mechanisim would the CR will compare these two different format successfully (but both have same type which is date now).

or

I shall use (Same as yours but a tweak it a little bit)

3- (cDate(ToText(cDate({DailyReport.ReportDate}),"MM/dd/yyyy")) >= {?DateRange}) AND  (CDate(ToText(cDate({DailyReport.ReportDate}), "MM/dd/yyyy")) >= {?DateRange})

Any confirmation is more appreciated

[daroy_alvin · Accepted solution]

Hi,

If you are going to use option 1, make sure that the two fields have the same format. "MM/dd/yyyy" = "MM/dd/yyyy". but I'm using No. 3 and it work fine to me.

Thanks and Regards,

Alvin

[ido_millet]

My guess is that your field is a DateTime data type, and that you need to change the parameter data type to DateTime. 

Or don't use the Expert, and build the expression yourself (Report, Selection Formula, Record...).

[Former Member]

Thanks for the reply.

The type of the field "ReportDate" in the MSSQL DB is Date, However, I tried in the report to make the Parameter Field (DateRange) both the Date and DateTime types and it is still does not show in the drop list.

I used

({DailyReport.ReportDate} >= {?DateRange} AND {DailyReport.ReportDate} <= {?DateRange})

and I got an error said a string is required here and {?DateRange} is high lighted.

For 1000% the type I used for {?DateRange} is Date and 1000% the type of ReportDate in MSQL is Date.

I have CR SP11

Appreciate your help

[ido_millet]

Sounds like {DailyReport.ReportDate} has a data type of String.

[Former Member]

But How!!!

In MSSQL side is type "Date" and in Report side is type "Date" too.

I create different DB in MSSQL and I created test table with date type and I got it same situation

Does CR create some where its own dataset?

I did Show Field Type in CR, Database Field and I really shocked !! The fields has been converted to String!! Why

So, My conclusion the Dataset (CR Database Fields) that created by CR is being converted. I do not know why and how to fix this situation

[Former Member]

Please can you tell why the type was converted from date to string ? and How to fix this problem