cannot export due to error: string length is less than 0 or not an integer

Crystal is throwing out over this formula with error in formula: string length is less than 0 or not  an integer.  The data is all stored in uppercase.  I'm testing for null.  What else can i do?


if isnull({Record.title}) or isnull({Record.GivenName}) or isnull({Record.Surname}) then
"Title, Given Name or Surname is null"
else
if left({Record.Surname}, 2) = "MC" then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "Mc" & lowercase(right({Record.Surname}, length({Record.Surname})-2))
  & ","
else if left({Record.Surname}, 3) = "MAC" then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "Mac" & lowercase(right({Record.Surname}, length({Record.Surname})-3))
  & ","
else if left({Record.Surname}, 2) = "O'" then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "O'" & uppercase(mid({Record.Title}, 3, 1)) & lowercase(right({Record.Surname}, length({Record.Surname})-3))
  & ","
else
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  uppercase(left({Record.Surname}, 1)) & lowercase(right({Record.Surname}, length({Record.Surname})-1))
  & ","
LVL 7
HecatonchiresAsked:
Who is Participating?
 
wykabryanConnect With a Mentor Commented:
My guess is since you are checking for nulls in your first statement you have a field that contains blanks, which are different than nulls.  Everything in your code appears to be correct, which would point me in that direction.  I would add this to the top.

if {Record.title}= "" or  {Record.GivenName} = "" or {Record.Surname}="" or
isnull({Record.title}) or isnull({Record.GivenName}) or isnull({Record.Surname})
   then "Title, Given Name or Surname is null"
0
 
OutinCommented:
What kind of database are you reporting from?

Is 'convert null values to default' in report options checked or not?

--
Outin
0
 
mlmccConnect With a Mentor Commented:
Do you have any surnames of just MAC or titles of a single character?  If so your length({String}) - 1 or 3 will be 0.

mlmcc
0
 
HecatonchiresAuthor Commented:
Hmm, I didn't get the usual 'your question has been updated' emails.

Anyway, thanks for the help.  I'll split evenly between wykabryan and mlmcc, You both helped ^_^

I ended up with:
if {Record.title}= "" or  {Record.GivenName} = "" or {Record.Surname}="" or isnull({Record.title}) or isnull({Record.GivenName}) or isnull({Record.Surname}) then
"Title, Given Name or Surname is null"
else
if left({Record.Surname}, 2) = "MC" and (length({Record.Surname})- 2) > 0 then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "Mc" & lowercase(right({Record.Surname}, length({Record.Surname})-2))
  & ","
else if left({Record.Surname}, 3) = "MAC" and (length({Record.Surname})- 3) > 0 then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "Mac" & lowercase(right({Record.Surname}, length({Record.Surname})-3))
  & ","
else if left({Record.Surname}, 2) = "O'" and (length({Record.Surname})- 2) > 0 then
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  "O'" & uppercase(mid({Record.Title}, 3, 1)) & lowercase(right({Record.Surname}, length({Record.Surname})-3))
  & ","
else
  "Dear " &
  uppercase(left({Record.Title}, 1)) & lowercase(right({Record.Title}, length({Record.Title})-1))
  & " " &
  uppercase(left({Record.Surname}, 1)) & lowercase(right({Record.Surname}, length({Record.Surname})-1))
  & ","
0
 
mlmccCommented:
Glad i could help

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.