split text for a lookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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))
 
Back
Top