Error in Record Selection with Select Case Function 2

[JEMorrison]

I'm getting a "The remaining text does not appear to be part of the formula." error in my record selection formula (CR8 reporting against an Access 2000 DB) when I attempt to use the following Select Case function (Crystal syntax). The 'remaining text' is the actual selection formula (last two lines of code).
I've only been using CR for about 6 months so any advice about what I'm doing wrong is appreciated.
Thanks,
Jamie

Local DateTimeVar EndDate;
Local DateTimeVar BegDate := DateSerial(Year(CurrentDate) - 1, 1, 1); 

select DatePart ("q",CurrentDate)
    case 1: 
        EndDate := DateSerial(Year(CurrentDate), 1, 1)
    case 2: 
        EndDate := DateSerial(Year(CurrentDate), 4, 1)
    case 3: 
        EndDate := DateSerial(Year(CurrentDate), 7, 1)
    case 4: 
        EndDate := DateSerial(Year(CurrentDate), 10, 1)
    default: 
        EndDate := DateSerial(Year(CurrentDate), 1, 1);

{FaceSheet_vw.Date Received} in [BegDate to_ EndDate] and 
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]

 

[synapsevampire]

Not sre, but try:

Local DateTimeVar EndDate;
Local DateTimeVar BegDate := DateSerial(Year(CurrentDate) - 1, 1, 1);

select DatePart ("q",CurrentDate)
case 1:
EndDate := DateSerial(Year(CurrentDate), 1, 1)
case 2:
EndDate := DateSerial(Year(CurrentDate), 4, 1)
case 3:
EndDate := DateSerial(Year(CurrentDate), 7, 1)
case 4:
EndDate := DateSerial(Year(CurrentDate), 10, 1)
default:
EndDate := DateSerial(Year(CurrentDate), 1, 1);

{FaceSheet_vw.Date Received} in [BegDate to EndDate] and
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]

You had an underscore next to the TO.

Anyway, it worked here if your intent is to merely return a true/false.

-k

-k

 

[JEMorrison]

The underscore next to the "to" tells Crystal that you want all dates in the range up to, but NOT including, the EndDate. Basicly, I'm telling Crystal to select dates using the logic: BegDate <= {FaceSheet_vw.Date Received} < EndDate .
You can use the underscore on either or both sides of the "to" if you wish to exclude either or both limits of your range.

-Jamie

 

[synapsevampire]

Ahhh, I see, thanks.

Anyway, the formula worked fine here without it, so try:

select DatePart ("q",CurrentDate)
case 1:
EndDate := DateSerial(Year(CurrentDate), 1, 1)
case 2:
EndDate := DateSerial(Year(CurrentDate), 4, 1)
case 3:
EndDate := DateSerial(Year(CurrentDate), 7, 1)
case 4:
EndDate := DateSerial(Year(CurrentDate), 10, 1)
default:
EndDate := DateSerial(Year(CurrentDate), 1, 1);
(
{FaceSheet_vw.Date Received} >= BegDate
and
{FaceSheet_vw.Date Received} < EndDate
)
and
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]

-k

 

[Turkbear]

Hi,
You might try adding an End Select statement after the last option..

select DatePart ("q",CurrentDate)
    case 1: 
        EndDate := DateSerial(Year(CurrentDate), 1, 1)
    case 2: 
        EndDate := DateSerial(Year(CurrentDate), 4, 1)
    case 3: 
        EndDate := DateSerial(Year(CurrentDate), 7, 1)
    case 4: 
        EndDate := DateSerial(Year(CurrentDate), 10, 1)
    default: 
        EndDate := DateSerial(Year(CurrentDate), 1, 1);

 End Select

It should not need it ( accordng to the 8.5 Help file on Crystal Syntax) , but I have had some older reports that would not work without it..

( I suspect it is version related).

 

[synapsevampire]

It dawned on me that your syntax is still wrong, try:

select DatePart ("q",CurrentDate)
case 1:
EndDate := DateSerial(Year(CurrentDate), 1, 1)
case 2:
EndDate := DateSerial(Year(CurrentDate), 4, 1)
case 3:
EndDate := DateSerial(Year(CurrentDate), 7, 1)
case 4:
EndDate := DateSerial(Year(CurrentDate), 10, 1)
default:
EndDate := DateSerial(Year(CurrentDate), 1, 1);
(
{FaceSheet_vw.Date Received} >= BegDate
and
{FaceSheet_vw.Date Received} < EndDate
)
and
(
not({FaceSheet_vw.Type of Complaint} in ["Grievance","Informal Comp"])
)

-k

 

[bdreed35]

Instead of the Select Case statement, try Switch and use some of Crystal built in Date Range functions.

Try this instead:

{FaceSheet_vw.Date Received} in
switch
(
    datepart("q",currentdate) = 1, minimum(LastYearYTD) to minimum(Calendar1stQtr),
    datepart("q",currentdate) = 2, minimum(LastYearYTD) to minimum(Calendar2ndQtr),
    datepart("q",currentdate) = 3, minimum(LastYearYTD) to minimum(Calendar3rdQtr),
    datepart("q",currentdate) = 4, minimum(LastYearYTD) to minimum(Calendar4thQtr)
)
and 
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]

This criteria will pass to you database as well.

~Brian

 

[JEMorrison]

I'm getting the same error (in the same place) with both your suggested changes. However, using if statements in place of the select case statements (see code sample below) doesn't produce the error (and works as expected).

- Jamie

Local DateTimeVar EndDate;
Local DateTimeVar BegDate := DateSerial(Year(CurrentDate) - 1, 1, 1); 

if DatePart ("q",CurrentDate) = 1 then EndDate := DateSerial(Year(CurrentDate), 1, 1) else
if DatePart ("q",CurrentDate) = 2 then EndDate := DateSerial(Year(CurrentDate), 4, 1) else
if DatePart ("q",CurrentDate) = 3 then EndDate := DateSerial(Year(CurrentDate), 7, 1) else
if DatePart ("q",CurrentDate) = 4 then EndDate := DateSerial(Year(CurrentDate), 10, 1) else
    EndDate := DateSerial(Year(CurrentDate), 1, 1);

{FaceSheet_vw.Date Received} in [BegDate to_ EndDate] and 
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]

 

[JEMorrison]

My apologies to Turkbear and bdreed35. I was responding to Synapsevampire when both of you responded, so my last message looks like I was responding to you. I will try your suggestions and then respond to the thread.

-Jamie

 

[JEMorrison]

Turkbear & Synapsevampire,

CR8.0 doesn't appear to recognize End Select (or even End). It made no difference and I received the same error. I appreciate both of your help very much and would welcome more ideas as to why my code doesn't work.


Brian (bdreed35),

Thank you for your alternative. It worked very well, and added several new functions to my toolbox.

Thanks again,

-Jamie

 

[Turkbear]

Hi,
The only thing I can think of ( especially on a Friday afternoon ) is that Access does not support the use of the Case statement in the way that Crystal produces it..

What is shown in the 'Show Sql' panel?

 

[JEMorrison]

Because the error prevents the code from actually being generated, I can't open the SQL panel.

-Jamie

 

[synapsevampire]

You need to be using ODBC to SHOW SQL in Access.

Not sure why your syntax didn't work, worked here in CR 9, this was Crystal syntax, not Basic.

-k

 

[rosemaryl]

Dear JEMorrison,

FYI, the correct Syntax to make it work is as follows (additons or corrections in red):

Local DateTimeVar EndDate;
Local DateTimeVar BegDate := DateSerial(Year(CurrentDate) - 1, 1, 1);
(
select DatePart ("q",CurrentDate)
case 1:
EndDate := DateSerial(Year(CurrentDate), 1, 1)
case 2:
EndDate := DateSerial(Year(CurrentDate), 4, 1)
case 3:
EndDate := DateSerial(Year(CurrentDate), 7, 1)
case 4:
EndDate := DateSerial(Year(CurrentDate), 10, 1)
default:
EndDate := DateSerial(Year(CurrentDate), 1, 1)
);

{FaceSheet_vw.Date Received} in [BegDate to_ EndDate] and
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]
) //nonintuative placement of paren

The first thing was that the select needed to be inclosed in parens and the statement separater ; placed outside the last paren for the select, then the last paren in the code is added counter-intuitively just because Crystal wants it there! Anytime you place a paren before the select it wants an extra paren at the end of the formula. This was a bug in this version that is corrected in CR10 for sure. I don't have 9.0 so I can't say.

Regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[rosemaryl]

Dear Jamie,

I didn't cut and paste enough, you will need one more paren at the end where indicated!
...
{FaceSheet_vw.Date Received} in [BegDate to_ EndDate] and
{FaceSheet_vw.Type of Complaint} <> ["Grievance","Informal Comp"]
[COLOR=red cyan])) [color]//nonintuative placement of paren

Also, since this doesn't get pushed down to sql - I recommend the switch statement as Brian indicated.

Best regards,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[JEMorrison]

Rosemary,

Thank you very much. This corrected the error and produced the expected results.

- Jamie