CODEWITHSUNDEEP
ms-accessvba
user3194940
user3194940

Reputation: 11

simple VBA function to build an address

I would like to build a function in MS Access that looks at 6 fields (Name, Address, City, State, ZIP, Country) in a table and display them all together like a recognizable address. It seems simple enough but I am running into trouble when attempting to check fields for those that are empty. For example, if I don't have a valid string in the Address field, I would like the function to display "invalid address". Any help would be appreciated. A simple piece of code would look like:

Function FullAddress(Name As String, Address As String, City As String, State As String, ZIP As String, Country As String)
If Address = "" Or City = "" Or Country = "" Then
    FullAddress = "invalid address"
Else
    FullAddress = Name & vbCrLf & Address & vbCrLf & City & ", " & State & "  " & vbCrLf & Country
End If
End Function

The function however results in "#Error" when any of the fields are blank.

Any ideas on what I am doing wrong?

Upvotes: 1

Views: 168

Answers (2)

Johnny Bones
Johnny Bones

Reputation: 8404

Try this:

Function FullAddress(Name As String, Address As String, City As String, State As String, ZIP As String, Country As String)
If Nz(Address, "") = "" Or Nz(City, "") = "" Or Nz(Country, "") = "" Then
    FullAddress = "invalid address"
Else
    FullAddress = Name & vbCrLf & Address & vbCrLf & City & ", " & State & "  " & vbCrLf & Country
End If
End Function

Upvotes: 0

HansUp
HansUp

Reputation: 97131

The issue is what those fields actually contain when you describe them as empty or blank. Two possibilities are ...

  1. zero-length string ("")
  2. Null

Most likely, the second is the cause of your problem. The reason is that all your function's parameters are declared as String. And Null is not a string value.

So you could change the declared datatypes from String to Variant so the function will accept Null values without complaint. But then you must revise the function body to cope with Nulls appropriately.

Or you can transform Null to zero-length string when you call the function. Within an Access session, you can use Nz to accomplish that transformation.

FullAddress(Nz([Name], ""), Nz([Address], ""), etc. )

Upvotes: 2

Related Questions