Solved

Compare only last 4 characters of phone number in SQL query

Posted on 2011-02-20
3
454 Views
Last Modified: 2012-05-11
I have the following SQl query I'm running against two tables in an Access 2007 database:

SELECT ContactPhone.Item_ID, ContactPhone.Last_Name, ContactPhone.First_Name, ContactPhone.Work_Phone, ContactPhone.[1st_Choice], ContactPhone.[2nd_Choice], ContactPhone.[3rd_Choice], ContactPhone.Comments
FROM ContactPhone LEFT JOIN onlyDCS ON ContactPhone.Work_Phone=onlyDCS.Work_Phone
WHERE (((onlyDCS.Work_Phone) Is Null));


I want to just use a substring compare of the last  four character of the "Work_Phone" from both tables.

example of the Work_Phone  data

555-1234

just need to compare

1234

Thanks!!
0
Comment
Question by:rkckjk
  • 2
3 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 34937695
you can only do this in the sql view of the query

select * from ContactPhone
where  right([ContactPhone ].[Work_Phone],4)=right([onlyDCS].[Work_Phone],4)
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34937703
SELECT ContactPhone.Item_ID, ContactPhone.Last_Name, ContactPhone.First_Name, ContactPhone.Work_Phone, ContactPhone.[1st_Choice], ContactPhone.[2nd_Choice], ContactPhone.[3rd_Choice], ContactPhone.Comments
FROM ContactPhone, onlyDCS
where  right([ContactPhone ].[Work_Phone],4)=right([onlyDCS].[Work_Phone],4)
 
0
 
LVL 2

Author Closing Comment

by:rkckjk
ID: 34937732
thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now