Solved

Access Control Textbox to go to a second line (carriage return) iif(isnull(variable)=False)

Posted on 2013-01-08
6
550 Views
Last Modified: 2013-01-08
Hello All
I have a query that gets the complete Street Address with Apt #  of Address Line 1 and also the complete Street Address with Apt # of Address Line 2. and then on a 3rd line the CSZ

Now what I want to do is to display that Line 1 and line 2 and Line 3 in a Control Textbox and IF Line 2 is not null show Line 2 on a new line and if Line 3 is not null show it on a new line.

Right now in my control source I have

=[AddrL1] & Chr(10) & Chr(13) & IIf(IsNull([AddrL2])=False,[AddrL2]) & Chr(10) & Chr(13) & iif(ixnull([CSZ])=false,[CSZ])

I also tried it as
=[AddrL1] & IIf(IsNull([AddrL2])=False, & Chr(10) & Chr(13)&[AddrL2]) & iif(ixnull([CSZ])=false,& Chr(10) & Chr(13)&[CSZ])


Is there a different way to do that instead of chr(10) & chr(13)  right now those aren't working in my situation
0
Comment
Question by:wlwebb
6 Comments
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Your syntax is a little off.  Try this:


=[AddrL1] & IIf(IsNull([AddrL2])=False, Chr(10) & Chr(13) & [AddrL2], "") &  IIf(IsNull([CSZ])=False, Chr(10) & Chr(13) & [CSZ], "")

Open in new window

0
 
LVL 39

Expert Comment

by:als315
Comment Utility
May be better to set it to:
=[AddrL1] & IIf(IsNull([AddrL2]), "",Chr(10) & Chr(13) & [AddrL2]) & iif(isnull([CSZ]),"",& Chr(10) & Chr(13)&[CSZ])
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
Also,  if you are dealing with a report, you can use separate textboxes for each line with the Can Shrink property of the textboxes set to true.  (This won't work on forms)

This works if you have no other controls in horizontal alignment with these textboxes.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:wlwebb
Comment Utility
Als315  I keep getting a Syntax error with that code

Mbiz.  shows all the info just doesn't go to a new line when I display the Form.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
try this


=[AddrL1] & IIf(IsNull([AddrL2])=False, Chr(13) & Chr(10) & [AddrL2], "") &  IIf(IsNull([CSZ])=False, Chr(13) & Chr(10) & [CSZ], "")
0
 

Author Closing Comment

by:wlwebb
Comment Utility
That was it Cap1
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now