Date Range Parameter

[dbielenda]

I have a date field that is a number in the database stored as 37190 that needs to be converted to Julian. However, there are some dates in the database stored as 99999 and these dates need to be displayed as 99/99/99 on my report. I used this formula to do all this saved as {@Date}:

if {WHSP999R.ACTDJR} = 99999 then "99/99/99"
else
ToText (DateValue ({WHSP999R.ACTDJR}+60), "MM/dd/yy&quot

Then I created a parameter called {?Actioned Date) that has a string value (this needs to be a string because the date will be passed as a string 01/23/02 using java, user will not even see Crystal). I also made this parameter a Range.

In the Record Selection Editor, I did:

{@Date} in {?Actioned Range}

Now, here comes the problem...
When I enter start range as 12/25/01 and End Range as 01/23/02, I get an error saying "The minimum cannot be larger than the maximum."

What can I do so that it recognizes that this date is earlier? If there is clarification needed, please do ask!

Thank you so much! s-)

 

[kab555]

The problem here is that the dates are text. When dates are text then 01/23/02 is less than 12/25/01 (01... comes before 12...)

You need to convert the text to date in order for your parameter to work.

 

[dbielenda]

Would I need to change the {@Date} formula or the formula in the Record Selection Editor?

 

[kab555]

You need to change the parameter type to Date. Then, you will need to convert the parameter to text in order for your record selection formula to work.

 

[dbielenda]

I did this in the record selection formula editor:
{@Date} in ToText({?Actioned Date})

and I get this error message: "A number, currency amount, boolean, date, time, date-time, or string is required here."
I also changed the parameter type to a Date instead of a string.

Why do I get this?

Thanks!

 

[dbielenda]

Even if I change it to a Discrete value, the report comes up blank!

 

[dgillz]

Dbielenda-

Lets back up for a moment. 37190 IS a julian datevalue is it not? This is what would display in excel if you formatted the date as a number.

I have a juliantodate UFL that may solve all your problems. It is suppossed to take a julian datevalue and convert it to a regular date JuliantoDate(37190) would return 10/26/01.

Interested? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dbielenda]

The formula that I use seems to do the trick. To be honest, I am afraid of doing anything else to this report except to get these date range parameters to work. Do you think this UFL will help at all?

The java coder takes my report and sends these date parameters and the report prints out blank. I have been trying different combinations and nothing seems to work. The value type the java coder will be passing is a string value. So I need to make sure that it remains this. Any suggestions in how I can fix this?

I even tried created a {?Start Actioned Date Range} and an {?End Actioned Date Range} and this did not seem to help much either. I am stumped...

 

[kenhamady]

I see a larger problem.

If you ask for all STRINGS between:

11/20/2001 and
11/29/2001


you will get the following dates included:

11/21/1998
11/21/1999
11/21/2000
11/21/2001 ...etc

Strings are sorted left to right.

You can do everything that you are currently doing in strings, but you are going to have to write a couple of extra formulas to convert your strings into dates, and use dates in your selection formula. Convert your 99/99/99 into 12/31/2050 or something like that. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

Ken is right on with regards to the strings and that is why you were getting the "Minimum cannot be larger than maximum" error in your first post.

If as I suggested you convert your julian values to dates, then compare them to the dates in your paramters, you will avoid all this. Also Ken's suggestion to check for 99/99/99 and convert it to some maximum (but legitimate) date is very solid. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dbielenda]

So this is what I ended up doing in the record selection formula editor:

DateValue({@Date}) in DateValue({?Start Actioned Date Range}) to DateValue({?End Actioned Date Range})

Seems to be working fine.. Now I need to deal with that 99/99/99...

Thank you all for your help!! s-)

 

[dbielenda]

How can I convert that 99999 to 12/31/2050, or something like this? Can you give me a formula example?

Thank you! s-)

 

[kenhamady]

with a Formula like:

if {@Date} = "99/99/99"
then Date (2050, 12,31)
else DateValue( {@Date} ) Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dbielenda]

Now that I changed all of these to a Datevalue, and the reports were tested, they come out blank for the java coder, but of course come out fine for me. Is there any way possible to leave these dates as a string? I tried something like:

{@Date} >= {?Start Actioned Date Range} and {@Date} <= {?End Actioned Date Range} and this did not work either when entering 12/01/00 to 01/29/02.

Anyone know of a work around for the date??

Thanks! s-)

 

[kenhamady]

If you broke the string apart into 3 pieces, and put them back together as:

001201

Something like:

{field} [ 7 to 8 ] +
{field} [ 1 to 2 ] +
{field} [ 4 to 5 ]

It would select and sort correctly, except for the century break where 99 would come after 00. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dbielenda]

Ken,

I'm sorry but I wouldn't know how to do this. I only know a few functions, do you think you could help me out with this one?

Thanks a bunch! s-)

 

[kenhamady]

Write the following formula putting your string field in place of the {field} below:

{field} [ 7 to 8 ] +
{field} [ 1 to 2 ] +
{field} [ 4 to 5 ]

It should convert your datestring into the string YYMMDD so that it sorts correctly and will select correctly. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com