Ignore blank columns and line space in query return?

[jdusn1978]

Memos Query - sort of looks like:

Expr1: MainTable.ID (the link between tables)
Expr2: Table2.Memo
Expr3: Table3.Memo
Expr4: Table4.Memo
Expr5. [Table2.Memo] + [Table3.Memo] + [Table4.Memo]

I've tried writing Expr5 as IIf, Is Not Null, IsNull etc and nothing works to remove blank space in Expr5 column

Example of how it looks now if Expr2 is blank:
Top of the textbox
----------------------------------------------
|
|
|
|
|
|
|
| Beginning of text from Expr3.Memo.
| Blank space above this ^^^^
| How do I remove this?
|
|
|
|


My network admin will not enable VBA/Macros so I am having to build everything based on queries and IIF statements, and everything else that isn’t very efficient.

This gives me a combination of all memos in Expr5. But sometimes, Table2, 3 or 4 is blank. In this case if Table2.Memo is blank, the memo text from Expr5 starts in the middle of the column instead of at the top. It even looks like a blank column until I expand it vertically to find the text in the middle. It also visually appears this way in the [Forms]![MemoQuery] ![Expr5.Textbox] and any reports.

How can I visually remove this space from empty Expr1, 2, or 3 tables so that it appears at the top of any and all text boxes?

Thank you in advance!

[PhilOfWalton]

Try
Expr5. Trim([Table2.Memo]) & Trim([Table3.Memo]) & Trim([Table4.Memo])

Always use &, not + for concatenating strings.
The Trim function should remove any spaces at the beginning and end of any strings. In fact you may have removed the space between the fields in which case try

Expr5 = Trim([Table2.Memo]) & " " & Trim([Table3.Memo]) & " " & Trim([Table4.Memo])

Phil

[jdusn1978]

Phil,

Thank you for the reply; however, both ways you suggested is still giving me the blank space any time the proceeding column expression is blank.

The memos are created via an IIf query. If I change the IIf query to Else False instead of Else being Null, all those empty lines are turned to 0's.

Does this mean something? It seems like it is not TRIMMING because it's actually empty, it just appears to be.

Not sure if that makes sense, really new at this and sysadmin through a wrench in my database dreams when the said they would not allow VBA Functions and Macros etc.

[PhilOfWalton]

OK, I'm a bit stumped. It is possible that you have a load of unprintable characters in your memos, probably linefeeds, but we need to know what we have.
The only way I can see to investigate is to write a little VBA on your local machine (Its only temporary to investigate further)

Public Function ShowTableField() As String
 
    Dim MyDb As Database
    Dim Rs As Recordset
 
    Set MyDb = CurrentDb
    Set Rs = MyDb.OpenRecordset("SELECT Table2.* FROM Table2;")
 
    With Rs
        'Do Until .EOF
            Debug.Print !Memo
            '.MoveNext
        'Loop
        .Close
        Set Rs = Nothing
    End With
 
End Function
 

You mat want to remove the comment lines to find the record you want.
If in the Immediate Window you type
?ShowTableField()

This will dump the contents of table2 memo to the immediate window.
Now copy everything (including what appears to be blank space) into a hex editor program like Hexplorer and let's see what we've got

Phil

[NeoPa]

I suspect there may be carriage returns or line feeds in your data. White space <> empty space.

This would mean the information you're giving us is inaccurate (albeit unintentionally so). I suspect this is where Phil is coming from also.

It may be easier for you to create a file with a Table2.Memo value in it which represents one of the lines where Expr2 takes up verticle space but shows no characters.

We're probably both going to find investigating the data in hex a bit easier than you might.

[jdusn1978]

Phil/NeopPa,

NeoPA, I think you’re right. You saying carriage returns made me think, the original data I’m trying to pull in is created using concatenating strings from other sources that include Chr(13) & Chr(10).

I’m really out of my depth there and probably wrote those poorly as well. And, I’m not leaving info out on purpose, just not sure how to correctly speak the lingo. BTW, this project is on a system that cannot talk to this system that connects to the internet.

Those, probably poorly written, concatenating strings look like:
= [Expr1] + Chr(13) & Chr(10) + [Expr2] + Chr(13) & Chr(10)+

This is where the Expr2: Table2, 3 or 4.Memo comes from.

Then, what I was asking about in my original question pulls this from each query and its respective table into one query so that as I click through the record the data is displayed in a textbox on a subform. I am running queries on top of queries to get around the fact that I am not allowed to run action code on the net.

I was under the impression that + Chr(13) & Chr(10) + would make the carriage return optional, as in only used if there’s something in the preceding [Expr]. Maybe it should an - AND IIf(IsNull[Expr1,Null,[Expr1] & Chr(13) & Chr(10))?

I will start a new thread question: How to write and expression that leaves out carriage returns if they are not needed?

[NeoPa]

jdusn1978:
And, I’m not leaving info out on purpose, just not sure how to correctly speak the lingo.

I don't imagine you are. Now I've seen even more of your posting style I'm pretty confident of that in fact.

You've handled the forum guidelines perfectly. Posting a separate question is exactly the way to go.

It's also an interesting question and it's already in my queue of items to look at.