Building a string in MS Access vba but exclude null value lines

I am trying to build a string with multiple lines for a text field that will print on a report. But not all the lines will always have values so I want to exclude those lines from the string if the values are null so there won't be blank lines where these null values are. How can I modify my string to exclude any of the lines that have null values? For example if the string Rndness is null then the ""Roundness: " & Rndness"  should not be shown on the report and instead of leaving a blank line I want it to pull all the lines after it up so there are no blanks.

DescMemo = "Material-Temper: " & MatTemper & vbNewLine
DescMemo = DescMemo & "Outer Diameter: " & OD & "-" & ODTol & vbNewLine
DescMemo = DescMemo & "Length: " & Length & " IN " & LengthOpt & " " & LengthTol & vbNewLine
DescMemo = DescMemo & "Roundness: " & Rndness & vbNewLine
DescMemo = DescMemo & "Straightness: " & Straightness & "/" & StraightnessUM & vbNewLine
DescMemo = DescMemo & "Hardness: " & Hardness & vbNewLine
DescMemo = DescMemo & "Surface Finish: " & SFinish & vbNewLine
DescMemo = DescMemo & "Bar End to Bar End: " & BEtoBE & vbNewLine
DescMemo = DescMemo & "Bar End Chamfer: " & BEChamfer & vbNewLine
DescMemo = DescMemo & "ASTM: " & ASTM & vbNewLine
DescMemo = DescMemo & "AMS: " & AMS & vbNewLine
DescMemo = DescMemo & "QQ: " & QQ & vbNewLine
DescMemo = DescMemo & "CDA: " & CDA & vbNewLine
DescMemo = DescMemo & "MIL: " & MIL & vbNewLine
DescMemo = DescMemo & "Other Spec: " & Other & vbNewLine
DescMemo = DescMemo & "Custom Requirements: " & CustReq

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well one trick is to use the + operator instead of the concatenation one (&).  This propagates null values.

So:

DescMemo = DescMemo + "Outer Diameter: " + OD + "-" + ODTol + vbNewLine

 Will result in that not being included if anything in the expression is null.

Jim.
1
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
So even the titles that I have on each line won't appear if there is at least one null value on that line? So if the OD string was null would the "Outer Diameter: " title along with everything else on that line not appear?
0
Roy CoxGroup Finance ManagerCommented:
I haven't much experience with Access VBA but can you use something like this

If Len(StraightnessUM) > 0 Then DescMemo = DescMemo & "Straightness: " & Straightness & "/" & StraightnessUM & vbNewLine

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
So even the titles that I have on each line won't appear if there is at least one null value on that line?

That is correct.  If there is a NULL anywhere in the expression, it is propagated through when using + and the resulting expression will be NULL.

Try this in the debug window:

? "Outer Diameter: " + OD + "-" + NULL + vbNewLine

and hit return.   You will see a NULL returned.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you very much, Jim! That's exactly what I needed!
0
NorieAnalyst Assistant Commented:
Lawrence

This is quite lengthy, and I know there are better ways, but it should work.
If Not IsNull(MatTemper) Then
    DescMemo = "Material-Temper: " & MatTemper & vbNewLine
End If

If Not IsNull(OD) Then
    DescMemo = DescMemo & "Outer Diameter: " & OD & "-" & ODTol & vbNewLine
End If

If Not IsNull(Length) Then
    DescMemo = DescMemo & "Length: " & Length & " IN " & LengthOpt & " " & LengthTol & vbNewLine
End If

If Not IsNull(Rndness) Then
    DescMemo = DescMemo & "Roundness: " & Rndness & vbNewLine
End If

If Not IsNull(Straightness) Then
    DescMemo = DescMemo & "Straightness: " & Straightness & "/" & StraightnessUM & vbNewLine
End If

If Not IsNull(Hardness) Then
    DescMemo = DescMemo & "Hardness: " & Hardness & vbNewLine
End If

If Not IsNull(SFinish) Then
    DescMemo = DescMemo & "Surface Finish: " & SFinish & vbNewLine
End If

If Not IsNull(BEtoBE) Then
    DescMemo = DescMemo & "Bar End to Bar End: " & BEtoBE & vbNewLine
End If

If Not IsNull(BEChamfer) Then
    DescMemo = DescMemo & "Bar End Chamfer: " & BEChamfer & vbNewLine
End If

If Not IsNull(ASTM) Then
    DescMemo = DescMemo & "ASTM: " & ASTM & vbNewLine
End If

If Not IsNull(AMS) Then
    DescMemo = DescMemo & "AMS: " & AMS & vbNewLine
End If

If Not IsNull(QQ) Then
    DescMemo = DescMemo & "QQ: " & QQ & vbNewLine
End If

If Not IsNull(CDA) Then
    DescMemo = DescMemo & "CDA: " & CDA & vbNewLine
End If

If Not IsNull(MIL) Then
    DescMemo = DescMemo & "MIL: " & MIL & vbNewLine
End If

If Not IsNull(Other) Then
    DescMemo = DescMemo & "Other Spec: " & Other & vbNewLine
End If

If Not IsNull(CustReq) Then
    DescMemo = DescMemo & "Custom Requirements: " & CustReq
End If

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
By the way, I would add a comment to the effect that you are using the + specifically for that purpose.

 It's not a standard way of concatenating strings and it's not always obvious that this was done on purpose.

Jim.
0
Fabrice LambertFabrice LambertCommented:
Afraid you'll have to test all your variables while building your string for the report:
DescMemo = vbNullString
If(MatTemper <> vbNullString) Then
    DescMemo  = DescMemo  & "Material-Temper: " & MatTemper & vbNewLine
End If

Open in new window

But we can do much better with an helper function and the iif operator:
DescMemo = vbNullString
DescMemo = DescMemo & iif(isNullOrEmpty(MatTemper, vbNullString, "Material-Temper: " & MatTemper & vbNewLine)

Open in new window

Public Function isNullOrEmpty(ByVal value As String) As Boolean
    value = Replace(value, vbCr, vbNullString)
    value = Replace(value, vbLf, vbNullString)
    isNullOrEmpty = Trim(value) = vbNullString
End Function

Open in new window

PS: Don't abuse the iif operator too much, as readability tend to suffer.
0
Fabrice LambertFabrice LambertCommented:
Well one trick is to use the + operator instead of the concatenation one (&).  This propagates null values.
Hmmm, that will only work with variant data types, wich I do not recommend in this case (nasty surprises can show up).

Speaking of data types, we do not know Lawrence's variables types.
I assumed it was string, wich do not support null values, you guys assumed something else.
And I suspect that Lawrence confuse null values with blank or empty strings, alas those are 2 things completly different.
0
aflockhartCommented:
Why doesn't the suggested and accepted solution result in losing all the previously constructed lines as well ?

e.g. Outer diameter" and Length are not null, so you add an Outer Diameter line and a Length line
But then Roundness is null, and you run:
DescMemo = DescMemo + "Roundness: " + Rndness + vbNewLine

and after this statement, DescMemo becomes null.
0
aflockhartCommented:
Don't have Access quickly available to check this- but if this turns out to be a problem, I'd split each line after the first into two stages; building each new line using "+" ; then concatenating the (possibly null) new line to the existing memo.

e.g.
DescMemo = "Material-Temper: " & MatTemper & vbNewLine

nextLine="Outer Diameter: " + OD + "-" + ODTol + vbNewLine
DescMemo = DescMemo & nextLine

nextLine= "Length: " + Length + " IN " + LengthOpt + " " + LengthTol + vbNewLine
DescMemo = DescMemo & nextLine

etc.

Also be aware that ANY null value on a line will cause that line to blank out.  So in the example above, if OD was not null, but ODTol was null, the whole line would be omitted,
1
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
@aflockhart,

  That is correct.    If you don't do that, the NULL would propagate all the way through and the result would be NULL.  I should have been clearer in my comment.

  You use the & and + to control where/when you want NULLs to propagate through.

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Note that I will un-accept the question if Lawrence or any of you feel that one of the other comments should be tagged with the solution or assists.

Jim.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.