Date range parameter but date is in a string

[bluevelvet]

Help! I need to set a date range parameter in my report (Crystal v8) but the date field in the database is actually a text string. I have a formula which converts the string to a date but of course can't use this in a parameter. Anyone out there know how to get round this???

 

[IdoMillet]

1. Keep the parameter as a Date Range.

2. Just use the converted text database field formula in the Record Selection Formula by comparing it to the Date Range parameter.

Cheers,
- Ido



CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[bluevelvet]

Thanks Ido, I understand the logic but am unsure of where to do this. Should I be using the record selection formula editor and if so do you think you could send me a sample of the syntax. I am working with 3 or 4 products and they all use differing sql syntax so I'm getting confused.

Thanks
Blue

 

[mbarron]

If you open then Select Expert you'll see a button on the lower right hand side called "Show Formula>>>" Clcik on it then click on the "Formula Editor" button.

You'll see your fields listed in the upper left pane.

Double click on the formula that creates your date the type "in" (no qoutes) the double click on the parameter field. It should look like this:

{@your.field} in {?parameter} Mike
If you're not part of the solution, you're part of the precipitate.

 

[bluevelvet]

Hey guys you are both angels!! This works so no more headaches for me, I can get on with producing the enormous pile of reports I have to do for our new system....

Thanks again.

Blue

 

[rhinok]

Using a formula as part of your Record Selection Criteria will cause all records to be returned and processed client-side (unless the formula is encapsulated in a boolean statement).

If you can, rebuild your string-to-date formula as a SQL Expression. You can then use the SQL Expression in your record selection statement in lieu of the formula. All the records should then be filtered server-side.

Check out the following FAQ: faq767-1377

 

[synapsevampire]

That's not always the case, Rhinok.

I have lots of formulas referenced in the record selection and the SQL gets passed, just make sure that you don't use variables in your formula, that appears to always break it.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[bluevelvet]

thanks everyone, I don't have variables in my formula so I should be ok, it certainly appears to be reasonably efficient from what I can see...

Blue