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

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
You should just concatenate the two characters not "add them up" with a +
Chr(13) & Chr(10)
 

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
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;
SQL:
=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

AWF VIP
Local time
Today, 16:50
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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.

1613066040548.png

"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

AWF VIP
Local time
Today, 16:50
Joined
Apr 13, 2010
Messages
1,401
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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
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
SQL:
=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

Super Moderator
Staff member
Local time
Today, 23:50
Joined
Feb 19, 2013
Messages
16,553
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]
 
Last edited:

Minty

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
Choose - I always, always forget about Choose... 🤦‍♂️
 

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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
SQL:
=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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
Ignoring the report then, does the query output display correctly?
 

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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

Super Moderator
Staff member
Local time
Today, 23:50
Joined
Feb 19, 2013
Messages
16,553
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

Lifelong Learner
Local time
Today, 16:50
Joined
Mar 14, 2017
Messages
8,738
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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
I was going to suggest the Rich Text setting but got distracted by the day job and forgot!
 

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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

AWF VIP
Local time
Today, 23:50
Joined
Jul 26, 2013
Messages
10,355
I think you need to use the PlainText() function.

PlaninText([MyRichTextField])
 

chuckcoleman

Registered User.
Local time
Today, 18:50
Joined
Aug 20, 2010
Messages
357
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

Super Moderator
Staff member
Local time
Today, 23:50
Joined
Sep 12, 2006
Messages
15,614
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.
 

Users who are viewing this thread

Top Bottom