Multiple fields in text box

[jibb]

Hello,

I have got a table with 5 fields of notes. Only up to 3 of the fields will have text in and I want to show only the fields with text in in 1 text box on a form.
I am currently using:

[notes1] + vbCrLf + [notes2] + vbCrLf + [notes3] etc...

but this shows the blank fields. Is there any way to exclude the blanks?

 

[vbaInet]

Use Null propgation.
http://office.microsoft.com/en-us/a...-lines-in-labels-and-reports-HA001149472.aspx

Aircode follows

(Notes1 + vbNewline) & (Notes2 +vbNewline)...

Ensure the parens and + and & characters are as I wrote them, but that was just an example., check out the link.

 

[jibb]

Its still returning blank lines...

 

[vbaInet]

That was aircode and I don't know if any of the fields contain zls or not. Did you read the link?

 

[missinglinq]

Despite being aircode, vbaInet's code should work unless, as he suggested, one or ore of the Fields has a Zero-Length String. While testing for ZLSs is frequently talked about, here and in other Access forums, the truth is that it is actually difficult to introduce one into an Access Field!Simply entering a Space or Spaces into a Control will not create a ZLS, nor will Deleting data . The only way i know to do it is to explicitly assign a ZLS to a Control, such as

Me.ControlName = ""

or to Copy/Import data from another program where ZLSs can occur.

Are you, perhaps, Importing data from another type of app into these Note Fields?

Linq ;0)>

 

[jibb]

I imported the table from Excel - will that import ZLS?

How can you clear ZLS from all fields in a table?

 

[vbaInet]

If the cells were tampered with then Yes. But that depends on the scenario.

Here's what you do:

1. Create an UPDATE query to set update fields that are "" to Null.
2. In the table design view, change the field's Allow Zero-Length String property to No.

That way you're sure that you're dealing with just Null.

 

[jibb]

I have checked for ZLS but all empty cells are null.

The code I'm using is as follows:

Me.txtCOMMENTS = (Me.cboCUSTOMER.Column(3) + vbNewLine) & (Me.cboCUSTOMER.Column(4) + vbNewLine) & (Me.cboCUSTOMER.Column(5) + vbNewLine) & Me.cboCUSTOMER.Column(6)

Are there any errors in it?

 

[vbaInet]

Did you perform step 2 or not?

 

[vbaInet]

Actually, wait there. The Column property itself is returning zls.

 

[jibb]

How do I sort that?

 

[vbaInet]

Well, you originally said textboxes but suddenly you mentioned Combo Boxes. What happened there? We need to know these things. Why aren't you using textboxes as per your OP?

 

[jibb]

The data is being returned to a text box. It is coming from a combo box which has all the notes in it.

 

[vbaInet]

Can you not use a subform?

 

[jibb]

It is a form with a lot of information on it which is controlled by 2 combo boxes with everything else locked.

 

[vbaInet]

Use something like an IIF() function to convert "" to Null.

Why's the last column, i.e. column 6, not in parens? Will it always have a value?

 

[jibb]

Where would I put the IIF in the formula?

The last column hasn't got () because if its blank you won't see it...there isn't more text after that column so it won't show as a blank.

 

[vbaInet]

You're testing the Column itself so you wrap the Me.Column(x) in IIF() testing using Len().

Alright, but what you will still end up with a blank newline if it's empty.

 

[jibb]

Sorted - Thanks for your help

 

[vbaInet]

No problemo!