Solved Expression Builder for address on Report (1 Viewer)

[monk_1987]

Hello

First off, I didn't write the code below, I took it from one of the Microsoft templates, so I am not 100% what it all does, as I am not the best at Access. However, it works very well if every field in the address boxes are filled in. The problem occurs when there is a null value in one of the address boxes and it just leaves a blank line. How do I write it so that if there is a null value there is no blank line, it just moves the line below up?

=IIf(IsNull([NumberOrBuildingName]);"";[NumberOrBuildingName]) & Chr(13) & Chr(10) &
IIf(IsNull([StreetAddress]);"";"" & [StreetAddress]) & Chr(13) & Chr(10) &
IIf(IsNull([AreaAddress]);"";"" & [AreaAddress]) & Chr(13) & Chr(10) &
IIf(IsNull([CountyAddress]);"";"" & [CountyAddress]) & Chr(13) & Chr(10) &
IIf(IsNull([PostCodeAddress]);"";"" & [PostCodeAddress])

Thanks for your time

 

[theDBguy]

If the field is Null (not ZLS), then you can take advantage of Null Propagation. For example,

[FirstName] & " " & ([MI] + " " + [LastName])

The above will only add one space between Firstname and Lastname if there is no middle initial.

 

[Gasman]

Looks like you do not have Null, but a space in an address field?
Perhaps change the check to

IIf(AddressFieldName & "" = "","",AddressFieldName & Chr(13) & Chr(10)) &

However I would have thought you only need CR & LF if the field is not empty?, in which case the CR & LF should be in the false part of the IIF() ?
IIf(IsNull([StreetAddress]);"";"" & [StreetAddress] & Chr(13) & Chr(10)) &

 

[plog]

Chr(13) & Chr(10) adds a return to the line. Right now you have hard coded 4 returns into that code. They are going to be in the output no matter what is in your fields. What you need to do is have those return only fire if there is a value in field. This means, you need to put them inside the Iif statements you have.

Logically, you don't need the field to be conditionally spit out--you can spit it out no matter what. If its null, its going to put nothing into the output, if it has a value it will put that value. Either way, whatever is inside the field has no effect on what you want to print. This means you can move the field outside the Iif.

I will do the first one for you, but the rest follow this pattern:

[NumberOrBuildingName] & IIf(IsNull([NumberOrBuildingName])=False, Chr(13) & Chr(10))

 

[monk_1987]

Chr(13) & Chr(10) adds a return to the line. Right now you have hard coded 4 returns into that code. They are going to be in the output no matter what is in your fields. What you need to do is have those return only fire if there is a value in field. This means, you need to put them inside the Iif statements you have.

Logically, you don't need the field to be conditionally spit out--you can spit it out no matter what. If its null, its going to put nothing into the output, if it has a value it will put that value. Either way, whatever is inside the field has no effect on what you want to print. This means you can move the field outside the Iif.

I will do the first one for you, but the rest follow this pattern:

[NumberOrBuildingName] & IIf(IsNull([NumberOrBuildingName])=False, Chr(13) & Chr(10))

Thanks for the reply and the code example. Appreciate you taking the time.

When I put it in I get the error 'The expression you entered contains an invalid syntax'

 

[plog]

And what is the expression you entered?

You used semi colons in your initial post instead of commas. Perhaps thats the issue--I might have a different version of Access which uses just commas.

 

[Eljefegeneo]

I ave used this for years and it works great. No spaces if anything is missing. You can adapt it to your particular application. With the proper carriage return codes, of course.

=([Title]) & (" "+[FirstName]) & (" "+[MiddleName]) & (" "+[Surname]) & (" "+[NameSuffix])

 

[monk_1987]

Chr(13) & Chr(10) adds a return to the line. Right now you have hard coded 4 returns into that code. They are going to be in the output no matter what is in your fields. What you need to do is have those return only fire if there is a value in field. This means, you need to put them inside the Iif statements you have.

Logically, you don't need the field to be conditionally spit out--you can spit it out no matter what. If its null, its going to put nothing into the output, if it has a value it will put that value. Either way, whatever is inside the field has no effect on what you want to print. This means you can move the field outside the Iif.

I will do the first one for you, but the rest follow this pattern:

[NumberOrBuildingName] & IIf(IsNull([NumberOrBuildingName])=False, Chr(13) & Chr(10))

You're a legend!!! I have figured it out and it works a treat.

For anyone else that wants to know, replace the comma ( , ) in the code with a semicolon ( ; ) and it works a treat.

 

[Gasman]

Only if that is your separator?
Mine is the , as I am in the UK

 

[MajP]

If doing this often then I would build a UDF to make this a lot easier toi mplement.
Then your query is simply
concatFields_Cr([street],[bldg],[city],[County] [state],[Country]) as CarriageReturnAddress
or
concatFields("; ",[street],[bldg],[city],[County],[state],[Country]) as SemiColonAddress

And you can do this with as many fields as you want.

Public Function ConcatFields(Delimiter As String, ParamArray Fields() As
Variant) As String
  Dim i As Integer
  For i = 0 To UBound(Fields)
    If ConcatFields = "" Then
      If Not (Fields(i) & "" = "") Then ConcatFields = Fields(i)
  
    Else
      If Not (Fields(i) & "" = "") Then ConcatFields = ConcatFields &
Delimiter & Fields(i)
    End If
  Next i
End Function

Public Function ConcatFields_CR(ParamArray Fields() As Variant) As String
  'So that you do not have to pass in a carriage return from the query
  Dim i As Integer
   For i = 0 To UBound(Fields)
    If ConcatFields_CR = "" Then
      If Not (Fields(i) & "" = "") Then ConcatFields_CR = Fields(i)
    Else
      If Not (Fields(i) & "" = "") Then ConcatFields_CR = ConcatFields_CR &
vbCrLf & Fields(i)
    End If
  Next i
End Function