Converting Access 2000 Syntax to CR 8.5 Formula 1

[Cheryl3D]

Hi:

I am trying to convert a complex immediate If statement (IIf) that was successfully generated in Access 2000 to a comparative one in a Crystal Reports 8.5 formula.

Here is the syntax in as it is in Access 2000, an IIf statement:

=IIf(IsNull([Revd_Coord]),""," Received on:" & Format([Revd_Coord],"Medium Date")) & IIf(IsNull([Sent_to]),""," Sent to:" & [Sent_to]) & IIf(IsNull([Date_Sent_for_Coord]),""," Sent On:" & Format([Date_Sent_for_Coord],"Medium Date")) & IIf(IsNull([Suspense_for_Coord]),""," Suspense:" & Format([Suspense_for_Coord],"Medium Date")) & IIf(IsNull([Coord_Complete]),""," Complete:" & Format([Coord_Complete],"Medium Date")),""))

This statement is for a field called Coordination on a report.

The result looks like the following example for those fields that are not null for a specific record:

Coordination: Suspense:15-Jun-99 Complete: 15-Jun-99

I attempted to do the same thing in a Crystal Reports 8.5 formula called Coordination.

In CR, my syntax looks like:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy") and
IF IsNull({qryAgreement.Sent_to}) then
" "
Else
"Sent To: "+ ToText(CDate({qryAgreement.Sent_to}),"dd-MMM-yy") and
IF IsNull({qryAgreement.Date_Sent_for_Coord}) then
" "
Else
"Sent On: "+ ToText(CDate({qryAgreement.Date_Sent_for_Coord}),"dd-MMM-yy") and
IF IsNull({qryAgreement.Suspense_for_Coord}) then
" "
Else
"Suspense: "+ ToText(CDate({qryAgreement.Suspense_for_Coord}),"dd-MMM-yy") and
IF IsNull({qryAgreement.Coord_Complete}) then
" "
Else
"Complete: "+ ToText(CDate({qryAgreement.Coord_Complete}),"dd-MMM-yy")


The 'qryAgreement' refers to a view used to generate the report.

When I ran just the first few lines of the formula, it ran correctly with the following syntax:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy")

If the Revd_Coord field was not null, the data appeared on the report like:

Received On: 15-Jun-99

But when I added the rest of the syntax like:
IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy") and
IF IsNull({qryAgreement.Sent_to}) then
" "
Else
"Sent To: "+ ToText(CDate({qryAgreement.Sent_to}),"dd-MMM-yy") and ...

I tried to save the formula in CR, and I received the following error message:

A boolean is required here.

I take it that I must have some error in my syntax.

Can anyone help to figure out what I'm doing incorrectly in CR?

Any help is most greatly appreciated.
Thanks,
Cheryl3D

 

[synapsevampire]

And doesn't mean concatenate, try:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Sent_to}) then
" "
Else
"Sent To: "+ ToText(CDate({qryAgreement.Sent_to}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Date_Sent_for_Coord}) then
" "
Else
"Sent On: "+ ToText(CDate({qryAgreement.Date_Sent_for_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Suspense_for_Coord}) then
" "
Else
"Suspense: "+ ToText(CDate({qryAgreement.Suspense_for_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Coord_Complete}) then
" "
Else
"Complete: "+ ToText(CDate({qryAgreement.Coord_Complete}),"dd-MMM-yy")

You can also use & as you did in VBA.

-k

 

[Cheryl3D]

Hi -k:

First thanks for the catch with the "and" you're correct I forgot that in CR + is used for concatenation.

I tried your SQL statement above in the formula. I checked the syntax and received the message from CR that there were no errors found. I saved it successfully.

But when I tried to run it in the report (via report preview), I received the following error message:

Bad date format string

Subsequently, the report could not be generated.

What is still wrong with my syntax?

I know that there should be a way to concatenation the entire formula so that the result looks like for example:

Suspense:15-Jun-99 Complete: 15-Jun-99

Thanks again for any help.
Cheryl3D

 

[synapsevampire]

Try posting what you tried rather than stating something didn't work.

The above was not a SQL Statement, it was to be used in a formula.

-k

 

[Cheryl3D]

Hi -k:

OK, fair enough.

Here is what I used as a formula:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Sent_to}) then
" "
Else
"Sent To: " + ToText(CDate({qryAgreement.Sent_to}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Date_Sent_for_Coord}) then
" "
Else
"Sent On: " + ToText(CDate({qryAgreement.Date_Sent_for_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Suspense_for_Coord}) then
" "
Else
"Suspense: " + ToText(CDate({qryAgreement.Suspense_for_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Coord_Complete}) then
" "
Else
"Complete: " + ToText(CDate({qryAgreement.Coord_Complete}),"dd-MMM-yy")

I did mean to offend anyone.
For some reason if I denote only the first portion of the formula it works just fine:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy")

But when I add just a few more lines to the formula like:

IF IsNull({qryAgreement.Revd_Coord}) then
" "
Else
"Received On: " + ToText(CDate({qryAgreement.Revd_Coord}),"dd-MMM-yy") +
IF IsNull({qryAgreement.Sent_to}) then
" "
Else
"Sent To: " + ToText(CDate({qryAgreement.Sent_to}),"dd-MMM-yy")

I received the 'Bad date format string' when I tried to preview the report.

Hope this helps a bit more to understand the problem.

Thanks again for your help.
Cheryl3D

 

[bdreed35]

Just so you know, you CR does support the IIF function.

I adjusted your original post. You will need to update it with your appropriate table names.

IIf(IsNull({table.Revd_Coord}),""," Received on:" & ToText({table.Revd_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({table.Sent_to}),""," Sent to:" & {table.Sent_to}) &
IIf(IsNull({table.Date_Sent_for_Coord}),""," Sent On:" & ToText({table.Date_Sent_for_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({table.Suspense_for_Coord}),""," Suspense:" & ToText({table.Suspense_for_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({table.Coord_Complete}),""," Complete:" & ToText({table.Coord_Complete},"dd-MMM-yyyy")),""))

~Brian

 

[synapsevampire]

I generally multiple nested IIFs as a courtesy to the next coder.

Perhaps there's a null date, or the database is passing a bad date, I'm not sure why it's giving this.

Try just placing the following field on the report:

"Test: "+ToText(CDate({qryAgreement.Coord_Complete}),"dd-MMM-yy")

Does that error?

If so, place just the date field on the report and see what happens.

-k

 

[Cheryl3D]

Thank you very much Brian:

It works just great!!! The only thing that I had to do was slightly change the very last portion ..., "")), so that the formula code that worked ended up looking like:

IIf(IsNull({qryAgreement.Revd_Coord}),""," Received on:" & ToText({qryAgreement.Revd_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({qryAgreement.Sent_to}),""," Sent to:" & {qryAgreement.Sent_to}) &
IIf(IsNull({qryAgreement.Date_Sent_for_Coord}),""," Sent On:" & ToText({qryAgreement.Date_Sent_for_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({qryAgreement.Suspense_for_Coord}),""," Suspense:" & ToText({qryAgreement.Suspense_for_Coord},"dd-MMM-yyyy")) &
IIf(IsNull({qryAgreement.Coord_Complete}),""," Complete:" & ToText({qryAgreement.Coord_Complete},"dd-MMM-yyyy"))

Thanks again for the fantastic assistance!!
Cheryl

 

[synapsevampire]

I see where the problem was now, you didn't need a cdate, they were already dates, the Format([Revd_Coord],"Medium Date")) threw me off.

Glad that Brian figured it out.

-k

 

[Cheryl3D]

Hi -k:

I noticed that I used the CDate conversion function which was incorrect. Thanks for pointing that out. Yes, I'm glad that Brian figured it out too.

I am a newbie at creating reports in CR. I was not aware that CR could handle nested immediate IIf functions like in MS Access. In this business, we often learn something new every day.

Thanks again for your help.
Cheryl