Can't eliminate empty lines (1 Viewer)

G

George-Bowyer

Registered User.
Local time
Today, 16:57
Joined
Dec 21, 2012
Messages
177
I have a report with a sub-report that shows an employees name and address.

If there happens not to be a relevant employee, then I have some code that searches a different table and comes up with a different address, which I load into an empty text box during the report details on format event

Code: 
        If Not RS.NoMatch Then
        
            If Not IsNull(!fldAdd1) Then strAdd1 = !fldAdd1
            If Not IsNull(!fldAdd2) Then strAdd2 = !fldAdd2
            If Not IsNull(!fldAdd3) Then strAdd3 = !fldAdd3
            If Not IsNull(!fldTown) Then strTown = !fldTown
            If Not IsNull(!fldRegion) Then strRegion = !fldRegion
            If Not IsNull(!fldPostCode) Then strPostCode = !fldPostCode
            
        End If
            
    End With
    
    strNewAdd = Trim(IIf(IsNull(strAdd1), "", strAdd1 + Chr(13) & Chr(10)) + IIf(IsNull(strAdd2), "", strAdd2 + Chr(13) & Chr(10)) + IIf(IsNull(strAdd3), "", strAdd3 + Chr(13) & Chr(10)) + IIf(IsNull(strTown), "", strTown + Chr(13) & Chr(10)) + IIf(IsNull(strRegion), "", strRegion + Chr(13) & Chr(10)) + IIf(IsNull(strPostCode), "", strPostCode))
    
    GetAddress = strNewAdd

For some reason this leaves an empty lines if one of the address fields is empty, and I can't work out why?

I suspect it must be that my empty fields are not null (which makes sense as they have already gone through "IF NOT ISNULL" process, bt I'm not sure what to do instead...?

Can anyone tell me how I need to sort this out, please?

Thanks

George
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 08:57
Joined
Oct 29, 2018
Messages
21,447
Hi. Yes, when you use IIf(IsNull(),""... you are replacing the nulls with an empty string and then add Chr(13) & Chr(10) to it, which results in an empty line when the field is null. Instead of using IIf(), you might try taking advantage of null propagation. For example:
Code: 
=[Field1] & (Chr(13) + Chr(10) + [Field2]) & (Chr(13) + Chr(10) + [Field3]) & etc...
When you add anything to null, the result is also null. So, if the field is null, then the carriage return gets nulled as well. Hope it helps...
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:57
Joined
May 21, 2018
Messages
8,519
Why would strAdd1 ever be null, isn't that a string? If not declare as string.

Code: 
       If Not RS.NoMatch Then
            strAdd1 = !fldAdd1 & ""
            strAdd2 = !fldAdd2 & ""
            strAdd3 = !fldAdd3 & ""
            strTown = !fldTown & ""
            strRegion = !fldRegion & ""
            strPostCode = !fldPostCode & ""
            
        End If

 Trim(IIf(strAdd1 = "", "", strAdd1 & Chr(13) & Chr(10))....
 
G

George-Bowyer

Registered User.
Local time
Today, 16:57
Joined
Dec 21, 2012
Messages
177
theDBguy said:
Hi. Yes, when you use IIf(IsNull(),""... you are replacing the nulls with an empty string and then add Chr(13) & Chr(10) to it, which results in an empty line when the field is null. Instead of using IIf(), you might try taking advantage of null propagation. For example:
Code: 
=[Field1] & (Chr(13) + Chr(10) + [Field2]) & (Chr(13) + Chr(10) + [Field3]) & etc...
When you add anything to null, the result is also null. So, if the field is null, then the carriage return gets nulled as well. Hope it helps...
Click to expand...

That's more or less where I started, although with slightly different distribution of + and &.

This works fine when used as a control source for a field in the (sub) report.

However, it isn't working when populating the field with VBA. I'm still getting an "empty line" in the address if, say, fldAdd3 is null.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 08:57
Joined
Oct 29, 2018
Messages
21,447
George-Bowyer said:
However, it isn't working when populating the field with VBA. I'm still getting an "empty line" in the address if, say, fldAdd3 is null.
Click to expand...
Hi George. Can you show us how you used it in VBA, so we could try to help you fix it? Thanks.


PS. I thought you could have tried something like:
Code: 
varName=[Field1] & (Chr(13) + Chr(10) + [Field2]) & (etc...)
 
G

George-Bowyer

Registered User.
Local time
Today, 16:57
Joined
Dec 21, 2012
Messages
177
MajP said:
Why would strAdd1 ever be null, isn't that a string? If not declare as string.

Code: 
       If Not RS.NoMatch Then
            strAdd1 = !fldAdd1 & ""
            strAdd2 = !fldAdd2 & ""
            strAdd3 = !fldAdd3 & ""
            strTown = !fldTown & ""
            strRegion = !fldRegion & ""
            strPostCode = !fldPostCode & ""
            
        End If

 Trim(IIf(strAdd1 = "", "", strAdd1 & Chr(13) & Chr(10))....
Click to expand...

Yep. That's nailed it. Thanks.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Today, 08:57
Joined
Oct 29, 2018
Messages
21,447
Ah. Glad to hear you got it sorted out. Good luck with your project.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:57
Joined
May 21, 2018
Messages
8,519
Having "" instead of null in the field that may have been the issue and this would check both. The best check for Access is to check for all "invisible" content. Null, empty string, space, spaces.
if trim(myControl.value & " ") = "" then

This however does not check for special characters (line feed, breaks, etc.), and would need more code for that.
 
You must log in or register to reply here.

Similar threads

M
Solved Expression Builder for address on Report
Replies
9
Views
515
MajP
MajP
C
Can't Get +Chr(13)+Chr(10) to work in a report, (Return plus line feed)
Replies
19
Views
2,120
gemma-the-husky
gemma-the-husky
T
New to the Forum!
Replies
13
Views
921
oleronesoftwares
oleronesoftwares
P
Line return from string to Field
Replies
4
Views
530
Micron
M
T
Solved Sending Email with Signature
Replies
3
Views
2,567
arnelgp
arnelgp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom