Solved

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

Posted on 2013-01-08
6
590 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
ID: 38754958
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 40

Expert Comment

by:als315
ID: 38754960
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
ID: 38754969
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:wlwebb
ID: 38755059
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38755075
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
ID: 38755087
That was it Cap1
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

828 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