Access VBA to Address1 & Address1 but Check for Partial Match

[TNewGuyDB]

Sorry if someone has answered this question but I was not able to find it by searching the forums or google and trust me I spent hours trying to find an answer. I am really a newbie at access and What I am trying to do is combine Address1 and Address2 but it needs to check if Address 2 has a partial match at the end of the text of Address 1 and if a match remove partial end data and combine Address1 & Address2. Also If not a match to combine Address1 and Address2. If someone can show me in a VBA, SQL, or something that would be a big help.

Example to check Addresses
Note: Don't Worry about the line "|" it is to show the two separate fields.
Address1 | Address2
4333 John St Unit 3 | Unit 3
42 32st NW | 88
1918 Rosewood Cir -1 | C-1
571 Kings Court 5 | #5
36 100th ave Unit 67 | #67

End Result Full Address
4333 John St Unit3
42 32st NW 88
1918 Rosewood Cir C-1
571 Kings Court #5
36 100th ave #67

[jimatqsi]

TNewGuyDB, welcome to Bytes. You've done a pretty good job spelling out your problem, which is uncommon for post #1.

This problem is pretty complex because of the nature of your content. You've shown some of the typical jumble of address data and I know from experience it can get a lot worse. I'll give you a start that you can build on. I'm not sure you can do this completely without a human hand from time to time. And frankly, your examples show some lack of clarity to your rules. Why Unit 67 #67 turns into #67 but Unit 3 turns into Unit3 and not #3 is not very clear.

First, we're doing this without context, so I don't if you're trying to do this in a query, or a form or a callable routine. I'll just give you the bare code and you'll ask if you don't know where to put it.

dim Add1 as string
dim Add2 as string
dim Add as string

' lets avoid the null string problem
add1 = nz(Address1) 
add2 = nz(Address2)
' make sure there are not trailing spaces
add1 = trim(add1)  
add2 = trim(add2)


' this next one may be incorrect; your examples are inconsistent
add1 = replace(add1,"unit","#") ' replace unit with hashtag
add2 = replace(add2,"unit","#") ' replace unit with hashtag
add1 = replace(ads1,"# ","#")    ' don't let spaces follow #
add2 = replace(ads2,"# ","#")    ' don't let spaces follow #

add1 = replace(add1,add2,"")  ' remove add 2 from add 1 if it is already there

add = Add1 & " " & add2  ' combine them into one string.

That will get you started. Revised and expand as needed.

Jim

[TNewGuyDB]

I just want to thank you for your example and it works great but I can not modify Address2 just add Address1 to Address2. If the last word/number/character from Address1 start or ends with Address 2 remove the last word and combine Address1 & Address2 together at the end. So what I am using is the If, Elseif or just If Statements in a Module to be used in a query. I been racking my brain to figure this out but with no luck. Also, If you look below this is what I can come up with but it doesn't work correctly.

Public Function ReplaceEnd(Add1 As String, Add2 As String) As String
' lets avoid the null string problem
Add1 = Nz(Add1)
Add2 = Nz(Add2)
' make sure there are not trailing spaces
Add1 = Trim(Add1)
Add2 = Trim(Add2)

'If Address1 last word ends with a letter/number/character from Address2 then remove last word From Address1
If Add1 Like "*Add2" Then
ReplaceEnd = Left(Add1, InStrRev(Add1, " ") - 1)
ReplaceEnd = Add1

'If Address last word starts with a letter/number/character from Address2 then remove last word From Address1 then remove last word
ElseIf Add1 Like "Add2*" Then
ReplaceEnd = Left(Add1, InStrRev(Add2, " ") - 1)
ReplaceEnd = Add1
Else
ReplaceEnd = Add1 & " " & Add2  ' combine them into one string.
End If
End Function

=============== =============== =============== =============== ========
Example1: If 3 is Like #3 Then replace last word from Address1 and Add Address2

Address1 Address2
1304 TUNNER ST 3 #3

Needs to be
1304 TUNNER ST #3
So 3 would be removed
=============== =============== =============== =============== ========

Example2: If 7D is Like 7D Dont replace last word from Address1 or Combine Address2

Address1 Address2
111 Hillcrest Ln 7D 7D

Needs to be just
111 Hillcreat LN 7D
=============== =============== =============== =============== ========

Example3: If -1 is like C-1 Then replace last word from Address1 and Add Address2

Address1 Address2
6309 132St -1 C-1

Needs to be just
6309 132St C-1
=============== =============== =============== =============== ========

Example4: If Address1 is not like Addrss2 combine Address 1 and Address2

Address1 Address2
784 John Cir 788 John Cir

This would just combine the two
784 John Cir 788 John Cir
=============== =============== =============== =============== ========

Example5: If Address2 is Null then leave Address1 alone
Address1 Address2
117 East Roger AVE

Would not change or Add Address2
117 East Roger Ave