asked on

C# Crystal Reports Formula

I have the below formula. It is being used in a group. When the middlename or nickname is null the group is not displaying a name. The values in the database is NULL. What is wrong with the formula?

'Return Name
Dim sFirstName as String
Dim sMiddleName as String
Dim sLastName as String
Dim sNickName as String

sLastName = {AccessEvents.LastName} 
sFirstName = {AccessEvents.FirstName} 
sMiddleName = IIF(Len ({AccessEvents.MiddleName}) <> 0, ", " + {AccessEvents.MiddleName}, "") 
sNickName = IIF(Len ({AccessEvents.NickName}) <> 0, ", " + {AccessEvents.NickName}, "")

If Len (sMiddleName) > 0 and Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName & sNickname
Elseif Len(sMiddleName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName 
Elseif Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sNickname
End If

Open in new window

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CipherIS

ASKER

Still not working. Wish there was a way to debug this.

Frank Helk

I'd check the other used fields (LastName, FirstName) for NULL, too ... i.e.

(...)
sLastName = IIf(IsNull({AccessEvents.LastName}),"-",{AccessEvents.LastName})
sFirstName = IIf(IsNull({AccessEvents.FirstName}),"-",{AccessEvents.FirstName})
(...)

Open in new window

Any emerging and uncatched NULL value would cause the formula to return nothing ...

CipherIS

ASKER

They are not null but I will add the check.

Frank Helk

Just another advice:

If I have problems in debugging such formulas, I first check the input values ... I add a new detail section (or group section) just below or above the regular one and just put the input fields of my formula into that section. Probably I try an additional forula with part results and output it there.

If I run that report then, it surely looks crude, but I could have a look on what I'm processing - in many cases that pushes me in a good direction ...

CipherIS

ASKER

I figured out the issue. Took me a while. So here is the original code.

If Len (sMiddleName) > 0 and Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName & sNickname
Elseif Len(sMiddleName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName 
Elseif Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sNickname
End If

Open in new window

Here is the fix. Look at the "Else" statement. That is what I needed. I did add the null check.

If Len (sMiddleName) > 0 and Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName & sNickname
Elseif Len(sMiddleName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sMiddleName 
Elseif Len(sNickName) > 0 Then
    Formula = sLastname & ", " & sFirstName & sNickname
Else
    Formula = sLastname & ", " & sFirstName 
End If

Open in new window