Nesting Iif statement with Mid and InStr Functions (1 Viewer)

[GretchenCh]

In Access 2010, I am using the following to add a hanging indent to the detail section of a 3 column report.

=IIf(Len([KeyArrRpt])<=45,[KeyArrRpt],Left([KeyArrRpt],InStr(45,[KeyArrRpt]," ")) & Chr(13) & Chr(10) & " " & Mid([KeyArrRpt],InStr(45,[KeyArrRpt]," ")+1))

I get the correct results for most of the records, but I am having two issues:

1. When the Len is less than 45, a blank line is being inserted before the result (it appears that the code is skipping over the 'true' part of the statement)

ex:
(hard return/blank line)
A PROPOS HAYDN * Wanek * ST06909 * ALF * $9.95

and

2. When the Len is >45 and the last string of the Left statement is over a certain length, it wraps to a new line and then continues the hanging indent on the next line instead of keeping the text together

ex:
10 EASY SONATINAS BY HUNGARIAN COMPOSERS *
Folio
* Vaczl * EMB3594 * B&H * $17.00

when it should look like:

10 EASY SONATINAS BY HUNGARIAN COMPOSERS * Folio
* Vaczl * EMB3594 * B&H * $17.00

Any suggestions on how to fix these issues? Any help would be much appreciated. Thanks!

 

[GretchenCh]

After some more testing I noticed that the above issues go away when I use rich text vs plain text, but the hanging indent also goes away. Also, when I use rich text, the shorter lines (<45 len) are indented at the beginning of the line.