split text for a lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Matt - 08 Sep 2005 15:29 GMT

The splitting function responses I've read explain how to split text if
there is a separator. I have a phone number field on my form that includes
the 10 digit telephone number. What I would like to do is take the first
three characters off this line, and place this in a new text field (area
code). Then I want to do a lookup on this field with a table I have that
includes all area codes in the US with the state in the second column. I
already have a lot of entries in my table so I don't want to just create a
field for my employee to input the area code separately. Does this make
sense? Any help would be greatly appreciated.

Reply to this Message

Klatuu - 08 Sep 2005 15:49 GMT

The Split function is not appropriate for what you want to do here. The
question is how the phone number is formatted. If it is just 555-123-9876
then it is easy:
Me.txtAreaCode = Left(Me.txtPhoneNumber,3)
On the other hand, if you have formatting like (555) 123-9876 then you need
to strip out the ( and the ). If this is the case, then:
Me.txtAreaCode = Mid(Me.txtPhoneNumber,2,3)
I would suggest you put in Input Mask on your phone number text box so you
will know exactly how it is formatted.

> The splitting function responses I've read explain how to split text if
> there is a separator. I have a phone number field on my form that includes
[quoted text clipped - 5 lines]
> field for my employee to input the area code separately. Does this make
> sense? Any help would be greatly appreciated.

Reply to this Message

Wayne Morgan - 08 Sep 2005 15:57 GMT

You can use the Left(), Mid(), and Right() functions to break apart the
string as you desire. I don't recommend placing the result in a new field in
the table though; however, a calculated field in a query would be ok. You
could also use the result in VBA to do a DLookup().

To get the first 3 characters:
strFirstThree = Left([PhoneNumber], 3)
strCity = DLookup("[City]", "[tblAreaCodes]", "[AreaCode]='" & strFirstThree
& "'")

This treats the value as a string/text. You will need to adjust the syntax
if the AreaCode field in the table is a Number data type instead of a Text
data type.

If a Number data type:
strCity = DLookup("[City]", "[tblAreaCodes]", "[AreaCode]=" &
CInt(strFirstThree))

Signature

Wayne Morgan
MS Access MVP

> The splitting function responses I've read explain how to split text if
> there is a separator. I have a phone number field on my form that
[quoted text clipped - 6 lines]
> field for my employee to input the area code separately. Does this make
> sense? Any help would be greatly appreciated.

Reply to this Message