By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,870 Members | 2,400 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,870 IT Pros & Developers. It's quick & easy.

Access VBA to Address1 & Address1 but Check for Partial Match

P: 2
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
Feb 20 '16 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 1,221
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.
Expand|Select|Wrap|Line Numbers
  1. dim Add1 as string
  2. dim Add2 as string
  3. dim Add as string
  5. ' lets avoid the null string problem
  6. add1 = nz(Address1) 
  7. add2 = nz(Address2)
  8. ' make sure there are not trailing spaces
  9. add1 = trim(add1)  
  10. add2 = trim(add2)
  13. ' this next one may be incorrect; your examples are inconsistent
  14. add1 = replace(add1,"unit","#") ' replace unit with hashtag
  15. add2 = replace(add2,"unit","#") ' replace unit with hashtag
  16. add1 = replace(ads1,"# ","#")    ' don't let spaces follow #
  17. add2 = replace(ads2,"# ","#")    ' don't let spaces follow #
  19. add1 = replace(add1,add2,"")  ' remove add 2 from add 1 if it is already there
  21. add = Add1 & " " & add2  ' combine them into one string.
That will get you started. Revised and expand as needed.

Feb 20 '16 #2

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. Public Function ReplaceEnd(Add1 As String, Add2 As String) As String
  2. ' lets avoid the null string problem
  3. Add1 = Nz(Add1)
  4. Add2 = Nz(Add2)
  5. ' make sure there are not trailing spaces
  6. Add1 = Trim(Add1)
  7. Add2 = Trim(Add2)
  9. 'If Address1 last word ends with a letter/number/character from Address2 then remove last word From Address1
  10. If Add1 Like "*Add2" Then
  11. ReplaceEnd = Left(Add1, InStrRev(Add1, " ") - 1)
  12. ReplaceEnd = Add1
  14. 'If Address last word starts with a letter/number/character from Address2 then remove last word From Address1 then remove last word
  15. ElseIf Add1 Like "Add2*" Then
  16. ReplaceEnd = Left(Add1, InStrRev(Add2, " ") - 1)
  17. ReplaceEnd = Add1
  18. Else
  19. ReplaceEnd = Add1 & " " & Add2  ' combine them into one string.
  20. End If
  21. 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
Feb 21 '16 #3

Post your reply

Sign in to post your reply or Sign up for a free account.