Blank fields in a report

[sugr74]

I have an address book which can have an empty field. When I run a report include the address i don't want to have the blank space appear on the report. I have managed to this on screen but as soon as i go to print preview it puts the spaces back in??

[Minty]

Try using this function to produce a calculated single address text field with CR between the elements for use on a report.

Public Function CanShrinkLines(ParamArray arrLines())
    ' Pass this function the lines to be combined
    ' For example: strAddress =
    ' CanShrinkLines(Name, Address1, Address2, City, St, Zip)
    Dim x     As Integer, strLine As String
    For x = 0 To UBound(arrLines)
        If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then
            strLine = strLine & arrLines(x) & vbCrLf
        End If
    Next
    CanShrinkLines = strLine
End Function

[sugr74]

That's way beyond my skills!! There must be a reason why it is doing it on screen but not whe converting to print preview??

[Minty]

Cut and paste is beyond you ?

Click on the file bar and select Create, then Module.
Paste the code exactly as above. Click save and close the VBA editor. Save the module with a name like modBasFunctions.

In your reports query create a new field exactly as per below, substituting your field names

strCompactAdd: CanShrinkLines(YourAddrField1, YourAddrField2, YourAddrField3, YourAddrField4, YourAddrField5)

In your report add the single field strCompactAdd as the control source for your address box.

The reason the on screen report is different is because by default the report preview form shrinks/removes "white space" as it doesn't have to do the actual printed page formatting.

[sugr74]

What do I need to remove from the code? Sorry but only been playing with access for a short while, but this is quite an important problem!

[sugr74]

I get "undefined function "canshrinklines" in expression". Must be doing something wrong

[Minty]

Have you saved the code module? What did you call it?
Can you copy and paste up the exact code from your database please

[sugr74]

Public Function canshrinklines(ParamArray arrLines())
' Pass this function the lines to be combined
' For example: strAddress =
' CanShrinkLines(Name, Address1, Address2, City, St, Zip)
Dim x As Integer, strLine As String
For x = 0 To UBound(arrLines)
If Not IsNull(arrLines(x)) And arrLines(x) <> "" Then
strLine = strLine & arrLines(x) & vbCrLf
End If
Next
canshrinklines = strLine
End Function


the fields i am trying to join are ADDRESS2,ADDRESS3,TOWN,COUNTY,PC




Actually ended up saving as anshrinklines

[Minty]

Actually ended up saving as anshrinklines

This is the problem - the module can't be called the same as any of the functions in the database. Rename the module

[sugr74]

I will try tomorrow, so the code is correct then?

[Minty]

I will try tomorrow, so the code is correct then?

It looks ok to me !

Once you have renamed the module you can test it really easily in the immediate window (Press ctrl + g in the vba window to make it visible)
Type the following in and press return

? canshrinklines("Big Town Industries",Null,"","Diagone Alley",Null,"Smallville")

And you should get

? canshrinklines("Big Town Industries",Null,"","Diagone Alley",Null,"Smallville")
Big Town Industries
Diagone Alley
Smallville

Which as you can see has no blank lines with either a null value or empty string "" passed in as values.

The ? means print the result so

?date()
09/11/2018

[sugr74]

Thank you so much for your help I will definitely try this tomorrow hopefully!

[sugr74]

I must be doing something wrong as it hasn't worked. I put strCompactAdd in as the control sauce for the address 2 field on the report and the result i get in #name? and still a gap

[sugr74]

I must be doing something wrong as it hasn't worked. I put strCompactAdd in as the control sauce for the address 2 field on the report and the result i get in #name? and still a gap.

It's only producing the first field that is not null.

[Minty]

What happens if you run the test in the immediate window - does that work ?

Please paste up how you are creating your strCompactAdd in the query.