If number is in a list then set field value

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

Guest

I have some data of phone numbers headed by their respective area codes:

224 312 630 773 815 847
2011462 2020356 1323562 2021797 2074066 2011033
2108768 2035678 1869414 2011462 2101544 2011376
2108926 2037780 2012696 2028915 2102712 2011462

How can I check if a number in another field, say 8472011462 is in the list?
I would need to first check the source area code of 847 from 8472011462 (the
source area code will always be the first 3 of the number). This give me the
area code. Next, I would need to check the appropriate col, i.e. the 847
col, then search it for the given number, i.e. 2011462. If found set a field
DNC for that record to be true, els false.

Thanks if you can help!
 
I the number you are looking up is in a text box named Text1, you could:
DLookup(Left([Text1], 3), "Table1", Mid([Text1,4))

However it would be much better to create a related table of phone numbers,
so that one client can have many phone numbers. This table would contain
fields:
ClientID relates to the primary key of your Client table
AreaCode the 3 digit area code.
Phone the remaining digits.
 
I'm relatively new at Access (been using Excel for years). The fields are
all number fields. How would I do this with numbers?

Allen Browne said:
I the number you are looking up is in a text box named Text1, you could:
DLookup(Left([Text1], 3), "Table1", Mid([Text1,4))

However it would be much better to create a related table of phone numbers,
so that one client can have many phone numbers. This table would contain
fields:
ClientID relates to the primary key of your Client table
AreaCode the 3 digit area code.
Phone the remaining digits.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rod said:
I have some data of phone numbers headed by their respective area codes:

224 312 630 773 815 847
2011462 2020356 1323562 2021797 2074066 2011033
2108768 2035678 1869414 2011462 2101544 2011376
2108926 2037780 2012696 2028915 2102712 2011462

How can I check if a number in another field, say 8472011462 is in the
list?
I would need to first check the source area code of 847 from 8472011462
(the
source area code will always be the first 3 of the number). This give me
the
area code. Next, I would need to check the appropriate col, i.e. the 847
col, then search it for the given number, i.e. 2011462. If found set a
field
DNC for that record to be true, els false.

Thanks if you can help!
Click to expand...
Click to expand...
 
You would not do this with numbers.

You would choose the Tables tab of the Database window, click New, and
create a table with the field names as suggested. (Presumably you already
have a Client table.) The fields would be of type Text, since phone numbers
may need to include brackets, spaces or other characters.

If you have not worked in Access before, this article will give you an
introduction:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209534

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Rod said:
I'm relatively new at Access (been using Excel for years). The fields are
all number fields. How would I do this with numbers?

Allen Browne said:
I the number you are looking up is in a text box named Text1, you could:
DLookup(Left([Text1], 3), "Table1", Mid([Text1,4))

However it would be much better to create a related table of phone
numbers,
so that one client can have many phone numbers. This table would contain
fields:
ClientID relates to the primary key of your Client table
AreaCode the 3 digit area code.
Phone the remaining digits.


Rod said:
I have some data of phone numbers headed by their respective area codes:

224 312 630 773 815 847
2011462 2020356 1323562 2021797 2074066 2011033
2108768 2035678 1869414 2011462 2101544 2011376
2108926 2037780 2012696 2028915 2102712 2011462

How can I check if a number in another field, say 8472011462 is in the
list?
I would need to first check the source area code of 847 from 8472011462
(the
source area code will always be the first 3 of the number). This give
me
the
area code. Next, I would need to check the appropriate col, i.e. the
847
col, then search it for the given number, i.e. 2011462. If found set a
field
DNC for that record to be true, els false.

Thanks if you can help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top