VBA Full Address

[Gerrit.B]

Is there a vba code to create a full address in one query field?

Now I have fields like (Name, Contact, Address1, Address1, Zip, City, Country)

I would create module to make one field from all these fields without error if one field is empty.

Name
Contact
Address1
Address2
Zip City
Country

Is this possible?

 

[mole999]

Just as a formula example if Name is in A1

G1 = =SUBSTITUTE(A1&", "&B1&", "&C1&", "&D1&", "&E1&", "&F1,", ,",",")

and drag down

 

[Gerrit.B]

I can't drag down in MS Access!

 

[alansidman]

You could do this in a query using concatenation, ie.

NewField: Name & ", " & Contact & ", " & ..........etc
This would show all separated by commas.

BTW: Name is reserved in Access and should not be used as it may cause issues. You would be better off using something like FName or LName or Name1 instead or some other variation.

 

[Gerrit.B]

Solved with module below:

Function FullAddress(CompanyName, Address1, Address2, ZIP, City, Country) As String

Dim A As String
Dim B As String
Dim C As String
Dim D As String
Dim E As String
Dim F As String


A = Trim(CompanyName)
B = nz(Trim(Address1), "")
C = nz(Trim(Address2), "")
D = nz(Trim(ZIP), "")
E = nz(Trim(City), "")
F = nz(Trim(Country), "")


'
FullAddress = IIf(A = "", "", A & Chr(13) & Chr(10)) & IIf(B = "", "", B & Chr(13) & Chr(10)) & IIf(C = "", "", C & Chr(13) & Chr(10)) & IIf(D = "", "", D & " ") & IIf(E = "", "", E & Chr(13) & Chr(10)) & IIf(F = "", "", F)


End Function

 

[mole999]

Done it again. missed that its in access, oops

 

[Micron]

Is there a vba code to create a full address in one query field?

Now I have fields like (Name, Contact, Address1, Address1, Zip, City, Country)

I would create module to make one field from all these fields without error if one field is empty.

Name
Contact
Address1
Address2
Zip City
Country

Is this possible?

Don't know if you care, but what you want to do violates normalization principles. Information like this should be 'assembled' by queries/forms/reports and not stored this way. What happens if Mr. Smith moves or gets a new phone number? You now have the same data stored in two places, which is bad enough, but also concatenated. There would be no easy or reliable way to alter part of your concatenated data.