?
Solved

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

Posted on 2013-01-08
6
Medium Priority
?
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

741 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