Can't Get +Chr(13)+Chr(10) to work in a report, (Return plus line feed)

[chuckcoleman]

I have a text box that has the following code in it. After a field, I want a "carriage return and a line feed" to put the next field on a new line. If I leave off the "&" before the +Chr(13)+Chr(10) when I run the report, that text box shows a #Type. If I leave remove the "&" before the +Chr(13)+Chr(10) it doesn't move the next field to a new line. What am I doing wrong?

=IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr] & +Chr(13)+Chr(10),IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr] & +Chr(13)+Chr(10),IIf([Forms]![Work Order Form]![Frame51]=3,"Change Order Nbr: " & [WorkNbr] & +Chr(13)+Chr(10)))) & "Appt Time: " & [AppointmentTime] & +Chr(13)+Chr(10) & IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & +Chr(13)+Chr(10),"") & IIf([Sub] Is Not Null,[Sub] & +Chr(13)+Chr(10),"") & "Zones: " & [Zone] & +Chr(13)+Chr(10) & "Notes=>" & [Other]

Thank you for your help! Chuck

 

[Minty]

You should just concatenate the two characters not "add them up" with a +
Chr(13) & Chr(10)

 

[chuckcoleman]

You should just concatenate the two characters not "add them up" with a +
Chr(13) & Chr(10)

I'm still having a problem. It still doesn't produce a new line with the next field. Here's the modified code:

=IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr] & Chr(13) & Chr(10),IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr] & Chr(13) & Chr(10),IIf([Forms]![Work Order Form]![Frame51]=3,"Change Order Nbr: " & [WorkNbr] & Chr(13) & Chr(10)))) & "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10) & IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"") & IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"") & "Zones: " & [Zone] & Chr(13) & Chr(10) & "Notes=>" & [Other]

 

[Minty]

That's a hell of mess to try and debug.

Split it into two or three text boxes with the individual bit in it and break it down.
Get each part working on it's own, then slowly add the parts back in.

I suspect a switch statement might be better, or if this is simply for display I'd move it into code and use a select case statement in the on current event.

I've formatted it to try and make it more readable;

=IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr] & Chr(13) & Chr(10),
    IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr] & Chr(13) & Chr(10),
        IIf([Forms]![Work Order Form]![Frame51]=3,"Change Order Nbr: " & [WorkNbr] & Chr(13) & Chr(10)))) 
& "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10) 
& IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"") 
& IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"") 
& "Zones: " & [Zone] & Chr(13) & Chr(10) 
& "Notes=>" & [Other]

 

[bastanu]

Maybe:

IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr]),IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr]),IIf([Forms]![Work Order Form]![Frame51]=3,"Change Order Nbr: " & [WorkNbr]))) & Chr(13) & Chr(10) & "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10) & IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"") & IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"") & "Zones: " & [Zone] & Chr(13) & Chr(10) & "Notes=>" & [Other]

Can you please show us the results?

Cheers,

 

[chuckcoleman]

Maybe:

IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr]),IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr]),IIf([Forms]![Work Order Form]![Frame51]=3,"Change Order Nbr: " & [WorkNbr]))) & Chr(13) & Chr(10) & "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10) & IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"") & IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"") & "Zones: " & [Zone] & Chr(13) & Chr(10) & "Notes=>" & [Other]

Can you please show us the results?

Cheers,

Still not working. In your code you have a ")" at the end of the first two iif statements. Removing the two ")" allows it to run, but still no CR/LF.

"Appt Time 9:00:00 AM" should be on a new line. Zones 0 should be on a new line. Notes=> Rich Belpedio should be on a new line.

 

[bastanu]

What happens if you move it to the report's record source (query) as a calculated field then use that as the control source of the text box on the report)?

 

[Minty]

Is the option group always 1, 2, or 3 e.g. it has to be one of those? If so it could be shortened to

=IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr],
    IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr] , "Change Order Nbr: " & [WorkNbr] ))
& Chr(13) & Chr(10)
& "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10)
& IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"")
& IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"")
& "Zones: " & [Zone] & Chr(13) & Chr(10)
& "Notes=>" & [Other]

 

[CJ_London]

or use the choose function

=choose([Forms]![Work Order Form]![Frame51],"Work Order Nbr: ","Proposal Nbr: ","Change Order Nbr: ") & [WorkNbr] & Chr(13) & Chr(10)
& "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10) & IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"") &
IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"") & "Zones: " & [Zone] & Chr(13) & Chr(10) & "Notes=>" & [Other]

 

[Minty]

Choose - I always, always forget about Choose...

 

[chuckcoleman]

Is the option group always 1, 2, or 3 e.g. it has to be one of those? If so it could be shortened to

=IIf([Forms]![Work Order Form]![Frame51]=2,"Proposal Nbr: " & [WorkNbr],
    IIf([Forms]![Work Order Form]![Frame51]=1,"Work Order Nbr: " & [WorkNbr] , "Change Order Nbr: " & [WorkNbr] ))
& Chr(13) & Chr(10)
& "Appt Time: " & [AppointmentTime] & Chr(13) & Chr(10)
& IIf([WOLocation] Is Not Null,"Location: " & [WOLocation] & Chr(13) & Chr(10),"")
& IIf([Sub] Is Not Null,[Sub] & Chr(13) & Chr(10),"")
& "Zones: " & [Zone] & Chr(13) & Chr(10)
& "Notes=>" & [Other]

Yes, it can only be a 1, 2 or 3. Your suggestion us essentially what "bastanu" said. Moving the CF/LF to the end of the nested iif statement.

I also copied the code, added it to a new field in the reports underling query and used that field as the source for the text box. That didn't work either.

 

[Minty]

Ignoring the report then, does the query output display correctly?

 

[chuckcoleman]

Ignoring the report then, does the query output display correctly?

Yes. When you run the query, since it's inserting a CR/Lf you have to use the down-arrow to see the results. But yes, you can see the correct data on different lines. Nobody has asked but the text box can grow and can shrink and the section can go and can shrink.

 

[CJ_London]

bit confused - is this now solved? Or is the display wrong on the report?

If it is still wrong, just check the control is not set to rich text - rich text does not used CR and LF, it uses <div> and </div>

 

[Isaac]

i agree with minty's suggestion and would add that's an impossible mess to look at. change it to a vba function that executes sequentially and it will be easier to create, document, troubleshoot & maintain

 

[Minty]

I was going to suggest the Rich Text setting but got distracted by the day job and forgot!

 

[chuckcoleman]

bit confused - is this now solved? Or is the display wrong on the report?

If it is still wrong, just check the control is not set to rich text - rich text does not used CR and LF, it uses <div> and </div>

Well, all it takes is for one simple question to solve a problem. The Text Box was set to Rich Text. I set it to Plain Text and it works.

NOW, one of the fields in the text box has it's data stored as Rich Text. How do I deal with that?

 

[Minty]

I think you need to use the PlainText() function.

PlaninText([MyRichTextField])

 

[chuckcoleman]

I think you need to use the PlainText() function.

PlaninText([MyRichTextField])

That took a while. Change text box to Plain Text from Rich Text and then use PlainText to address the field with underlying Rich Text.

THANK YOU ALL! SOLVED

 

[gemma-the-husky]

i agree with minty's suggestion and would add that's an impossible mess to look at. change it to a vba function that executes sequentially and it will be easier to create, document, troubleshoot & maintain

I agree with a function.
Much easier to manage and test.