Converting an address? 2

[scoobey]

I have a very small, but annoying issue. I have the following standard fields in a form:

Address 1
Address 2
Address 3
Town
County
Post Code

I also have another field called FULL ADDRESS, which combines the above fields into 1 text box. I need to separate each line with a comma, so that it looks like this in the FULL ADDRESS text box. For example:

313 Broadwater Close, Hitchin, Herts SG4 2PP

Any ideas on how I would code this? Don't forget that not all users will necessarily know the town/county or have an Address 2/Address 3. I have tried but I can't seem to get the code to not include an extra comma if details are missing:

313 Broadwater Close,, Herts SG4 2PP

Any help would be very much appreciated. Thanks....

 

[nat1967]

hi scoobey,

something like this should work if I understand correctly:

'****begin code******
Private Sub cmdCombine_Click()
Dim strAddress As String

strAddress = ""

If Not IsNull(Me.txtAddress1) Then
strAddress = Me.txtAddress1
End If

If Not IsNull(Me.txtAddress2) Then
strAddress = strAddress & ", " & Me.txtAddress2
End If

If Not IsNull(Me.txtAddress3) Then
strAddress = strAddress & ", " & Me.txtAddress3
End If

If Not IsNull(Me.txtTown) Then
strAddress = strAddress & ", " & Me.txtTown
End If

If Not IsNull(Me.txtCounty) Then
strAddress = strAddress & ", " & Me.txtCounty
End If

If Not IsNull(Me.txtPostal) Then
strAddress = strAddress & " " & Me.txtPostal
End If


Me!txtCombined = strAddress


End Sub
'*****end code*****

the code above will look at each text box to see if it is filled in, if so, build the string appropriately.

HTH


Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[scoobey]

Thanks a lot Nathan - it worked a treat!

Cheers...

 

[Aubs010]

scoobey,

Just out of curiosity, looking at the code, if Me.txtAddress1 is null, then don't put it into the string. BUT, if Me.txtAddress2 is not null, put it into the string with a comma before it. I know it wasn't a requirement, but in some cases, people may (and probably will!) leave out the Address1...

Thus, if Me.txtAddress1 is null in any situation then the address will look like this:


, Address2, Address3, Town, County Postal

I know this code is a bit of a mouthful, but it does work!!

[Site Address1] & IIf([Site Address1] Is Not Null,IIf([Site Address2] Is Null And [Site Address3] Is Null And [Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", "),"") & [Site Address2] & IIf([Site Address2] Is Not Null,IIf([Site Address3] Is Null And [Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", "),"") & [Site Address3] & IIf([Site Address3] Is Not Null,IIf([Site Town/City] Is Null And [Site County] Is Null And [Site PostCode] Is Null,".",", "),"") & [Site Town/City] & IIf([Site Town/City] Is Not Null,IIf([Site County] Is Null And [Site PostCode] Is Null,".",", "),"") & [Site County] & IIf([Site County] Is Not Null,IIf([Site PostCode] Is Null,".",", "),"") & [Site PostCode] & IIf([Site PostCode] Is Not Null,".","")

Anyways, keep smiling!!

Aubs

 

[grtammi]

Aubs:

While your post is of assistance Aubs, it could tend to be very confusing for most users. You can see by the sheer size about of bracketing / punctuation / text involved, that it would be very difficult to debug should the situation occur.

scoobey:

Aubs has a good point that the address you end up with could be malformed, should txtAddress1 be null. I would urge you to continue with nathan1967's post, but modify it slightly, as follows:

In each of your If Not IsNull(expression) statements, add the following code BEFORE you append the data onto strAddress:

   If strAddress <> &quot;&quot; Then
      strAddress = strAddress & &quot;, &quot;
   End If

Using the above code will then filter out the case of having the extra comma show up when you don't want it to.

And in each one of your strAddress = strAddress & &quot;, &quot; & <appended data> assignments, remove the & &quot;, &quot; part - you won't need it anymore.

HTH


Greg Tammi, IT Design & Consultation
Work:

http://www.atsbell.com

Home:

http://members.shaw.ca/gregandliz

 

[Aubs010]

Greg,

Good point!!!

I do like your reply

and thanks for the better way of doing it!

Scoobey: Listen to Greg! ;o)

Ta!



Aubs

 

[scoobey]

Cheers - thanks for your help on this one chaps. Much appreciated!