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

Remove directionals from a number

P: n/a
All,

I am not well versed in writing code and was looking for some
assistance.

I have the following info.

housenumber directional
1143
1142 N
1134 NE
1135 B

What I am trying to accomplish is the following:

housenumber directional
1143
1142 N
1134 NE
1135 B

This would only pertain to N, NE, NW, S, SE and SW

Any help is greatly appreciated.

Apr 27 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

<Sm******@aol.com> schreef in bericht news:11**********************@y43g2000cwc.googlegr oups.com...
All,

I am not well versed in writing code and was looking for some
assistance.

I have the following info.

housenumber directional
1143
1142 N
1134 NE
1135 B

What I am trying to accomplish is the following:

housenumber directional
1143
1142 N
1134 NE
1135 B

This would only pertain to N, NE, NW, S, SE and SW

Any help is greatly appreciated.


Maybe you can adapt this: (also add err trapping)

Function Test(strValue As String) As String
Dim lngNum As Long 'Number part
Dim strOther As String 'Other part
strValue = Trim(strValue) 'remove spaces
lngNum = Val(strValue)
If lngNum > 0 Then 'we found the number part here
strOther = Trim(Mid(strValue, Len(lngNum) + 1))
End If
Select Case strOther
Case "N", "NE", "NW", "S", "SE", "SW"
Test = Trim(Left(strValue, Len(strValue) - Len(strOther))) & " -- " & strOther
Case Else
Test = strValue
End Select
End Function

Arno R

Apr 27 '06 #2

P: n/a
On 27 Apr 2006 09:56:11 -0700, Sm******@aol.com wrote:
All,

I am not well versed in writing code and was looking for some
assistance.

I have the following info.

housenumber directional
1143
1142 N
1134 NE
1135 B

What I am trying to accomplish is the following:

housenumber directional
1143
1142 N
1134 NE
1135 B

This would only pertain to N, NE, NW, S, SE and SW

Any help is greatly appreciated.


What Access version?

How do you know that the "N" in 1142 N is not the apartment number,
rather than a directional?
What about E and W?
What about numbers like 1142 1/2 N

Back up your data first.

If your version supports the InStrRev() function,

Add a new Module to your database with these 2 functions:
NOTE: I've added "E" and "W" to the directions. If you don't want
them, don't include them below.

Function FindHouseNumber(FieldIn As String) As String
Dim strValue As String
Dim Var As Variant
strValue = Mid(FieldIn, InStrRev(FieldIn, " ") + 1)
Select Case strValue
Case "N", "NW", "E", "W", "S", "N", "NE", "SE", "SW"
FindHouseNumber = Left(FieldIn, InStrRev(FieldIn, " ") - 1)
Case Else
FindHouseNumber = FieldIn
End Select

End Function
==================

Public Function FindDirectional(FieldIn As String)
Dim strValue As String
strValue = Mid(FieldIn, InStrRev(FieldIn, " ") + 1)
Select Case strValue
Case "N", "NW", "E", "W", "S", "N", "NE", "SE", "SW"
FindDirectional = strValue
Case Else
FindDirectional = Null
End Select

End Function
=============
To permanently split the data, create an Update query:

UPDATE TableName SET TableName.Directional =
FindDirectional([HouseNumber]), TableName.HouseNumber =
FindHouseNumber([HouseNumber])
WHERE (((TableName.HouseNumber) Is Not Null));

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
Apr 27 '06 #3

P: n/a
Thanks Fred. It worked great.

Apr 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.