[jans78]

Appreciate if you all can help me to solve my Crystal Report problems
First, I create some parameters and one of the parameters is Date. I set the parameter for the date is String.
For example :
parameter 1 - From Collection Date
parameter 2 - To Collection Date

I should display the results base on this selection criteria :
1) specific date - to enter specific date in FROM and TO field eg 20071227 to 20071227
2) greater or equal date - enter at FROM date field eg 20071227 to (null). System will display data with date 20071227 and greater.
3) less or equal date - enter at TO date field eg (null) to 20071227. System will display data with date less than until 20071227.
4) range of dates - to enter at FROM date field and TO date field eg 20071201 to 20071227. System will display data with date between 20071201 and 20071227.

I create a formula at Record Selection (Select Expert) as per below:

(If {?Collection Date} <> 'ALL' and {?To Collection Date} <> 'ALL' Then
ToText({CPS_DM05_VW.BATCH_DATE}) IN ToText({?Collection Date}) to ToText({?To Collection Date})
Else if {?Collection Date} <> 'ALL' and {?To Collection Date} = 'ALL' Then
ToText({CPS_DM05_VW.BATCH_DATE}) <= ToText({?Collection Date})
Else if {?To Collection Date} <> 'ALL' and {?Collection Date} = 'ALL'Then
ToText({CPS_DM05_VW.BATCH_DATE}) >= ToText({?To Collection Date})
Else
True)

The problem is...I cannot get the right data base on selection criteria for No. 2 and 3
I hope you can help me to solve this problem.
TQSM

[DrewAllen]

First, are the parameters 'ALL' or null? In your description, it sounds like they are null, but you are comparing them to 'ALL' in your formula. If they can be null, then you need to check their null status FIRST, otherwise Crystal will shortcircuit the evaluation process when it finds an unhandled null.

Second, it is a very bad idea to use String parameters to enter Date values. When you use String parameters, your user can enter ANY string. You then have to include lots of error checking to ensure that the string encodes a valid date and also to ensure that the date is in the format that you require. When you use a date parameter, Crystal does all of this checking for you.

For instance, are you set up to handle all of the following strings:

20080102
Any
2008-01-02
All
02.01.2008 (dd.mm.yyyy)
XYZZY
01/02/2008 (mm/dd/yyyy)
02 Jan 2008
January 2, 2008

Using Date parameters will also prevent problems with converting your date fields to strings. Since you used the ToText(x) version of the function, the date is converted using the default format based on the regional settings on the user's computer. Different users will get different results based on their settings. If you want to force a particular format, you need to specify the format string using the version ToText(x, y).

Drew

[jans78]

Actually, i want to display all data if user select 'ALL' from the drop down menu. Parameter that i used is String and Static parameter. I not set up the format to be entered. I just test enter date using this format. I already set the date format as YYYYMMDD, but it cannot works. I hope you can help me.

m/d/yyyy - 12/4/2007

If i want use Date parameters, can you help me how to modify my script. I'm still a beginner and still learning the Crystal Report.

TQSM

[stancliff]

I use a Parameter of Type Date.

Here are my Options settings:
Allow custom values: TRUE
Allow multiple values: FALSE
Allow discrete values: FALSE
Allow range values: TRUE

In my select expert, this formula exists: {GIFT.DTE} in {?Date Range}, where Date Range is the name of the parameter.

When the report is executed, the user is presented with one date parameter, where the user can enter a Start of Range value and an End of Range value. For each value, the user is also presented with two selection boxes. One is "include this value" and one is "no lower/upper value". So, if the user eneterd 12/31/2007 as the End of Range value, and left Start of Range blank, then the report would run for all dates on or before 12/31/2007.

I am using Crystal Reports XI.

Does this help?

-Brian

[DrewAllen]

I suggest that you use Brian's method. It's very clean and simple and you don't have to worry about converting data.

As it stands, there are several problems with your formula.

• Your parameters are Strings, but you are using ToText(Parameter) which does nothing.
• Since you are not using the format string for your Batch Date, you are getting the system default mm/d/yyyy.
• Your parameters are in the format yyyymmdd but your batch date is in the format mm/d/yyyy.
• If you are using dates within the last five years or so, all of your parameters will begin with "20", but all of your converted batch dates will begin anything from 01 through 12.
• Any string beginning with "12" is less than any string beginning with "20", so the converted dates never fall in the range.
• Remember you are doing a string comparison here, because everything is a string.

If you don't understand what I'm saying here. Create a separate formula for each ToText() that you are using in your main formula, so that you can see the results of just that conversion.

The main point to remember is that strings are strings are strings. Even if they look like dates, they are strings. If you want them to behave like dates, you need to convert them to dates.

Drew

[stancliff]

I just learned that I could attach a graphic to these forums. I'm attaching a screen shot of how my date range parameter appears in Crystal that I described above.

-Brian

[jans78]

Hi...thank you...i already get the solution. I still using string for the date parameter. I create a Formula Field to convert the date to string.

At Formula Field, i create this formula

DIM MM,DD,YY,STR1
STR1= SPLIT(TOTEXT({NPCS_INPUT_TXN.TXN_DATE}),"/")
IF LEN(STR1(1)) = 1 THEN
MM = "0" & STR1(1)
ELSE
MM = STR1(1)
END IF
IF LEN(STR1(2)) = 1 THEN
DD = "0" & STR1(2)
ELSE
DD = STR1(2)
END IF
FORMULA= STR1(3) & MM & DD

and at Select Expert...

(IF {?Collection Date} <> "ALL" AND {?To Collection Date} <> "ALL" THEN
TOTEXT({@ColDate}) IN {?Collection Date} TO {?To Collection Date}
ELSE IF {?Collection Date} <> "ALL" AND {?To Collection Date} = "ALL" THEN
TOTEXT({@ColDate}) >= {?Collection Date}
ELSE IF {?Collection Date} = "ALL" AND {?To Collection Date} <> "ALL" THEN
TOTEXT({@ColDate}) <= {?To Collection Date}
ELSE
TRUE)