duplicate Phone search

[lmv]

I have a search form with a query but it is returning records where the phone
field is blank as well as the ones that are dupes.
How can I filter out the records where phone # is blank?
Thanks!

SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE ((([Name List].PhoneNumber) In (SELECT [PhoneNumber] FROM [Name List]
As Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 And [PhoneNumber] = [Name
List].[PhoneNumber])) AND (([Name List].Inactive)=False))
ORDER BY [Name List].PhoneNumber;

 

[Jeanette Cunningham]

Like this-->

SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE ((([Name List].PhoneNumber) In (SELECT [PhoneNumber] FROM [Name List]
As Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 And [PhoneNumber] = [Name
List].[PhoneNumber])) AND (([Name List].Inactive)=False)) AND (([Name List]
Is Not Null))
ORDER BY [Name List].PhoneNumber;


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Spencer]

SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE [Name List].PhoneNumber) In
(SELECT [PhoneNumber]
FROM [Name List] As Tmp
WHERE PhoneNumber <> "" and PhoneNumber is Not Null
GROUP BY [PhoneNumber]
HAVING Count(*)>1
And [PhoneNumber] = [Name List].[PhoneNumber])
AND [Name List].Inactive=False
ORDER BY [Name List].PhoneNumber;

Or

SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE [Name List].PhoneNumber) In
(SELECT [PhoneNumber]
FROM [Name List] As Tmp
GROUP BY [PhoneNumber]
HAVING Count(*)>1
And [PhoneNumber] = [Name List].[PhoneNumber])
AND [Name List].Inactive=False
AND PhoneNumber <> "" and PhoneNumber is Not Null
ORDER BY [Name List].PhoneNumber;



John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Try this --
SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE ((([Name List].PhoneNumber) In (SELECT [PhoneNumber] FROM [Name List]
As Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 And [PhoneNumber] = [Name
List].[PhoneNumber])) AND (([Name List].Inactive)=False)) AND [Name
List].[PhoneNumber] Is Not Null AND [Name List].[PhoneNumber] <>""
ORDER BY [Name List].PhoneNumber;

 

[lmv]

Brilliant! Thanks Karl!

Try this --
SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE ((([Name List].PhoneNumber) In (SELECT [PhoneNumber] FROM [Name List]
As Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 And [PhoneNumber] = [Name
List].[PhoneNumber])) AND (([Name List].Inactive)=False)) AND [Name
List].[PhoneNumber] Is Not Null AND [Name List].[PhoneNumber] <>""
ORDER BY [Name List].PhoneNumber;

--
Build a little, test a little.

I have a search form with a query but it is returning records where the phone
field is blank as well as the ones that are dupes.
How can I filter out the records where phone # is blank?
Thanks!

SELECT DISTINCT [Name List].PhoneNumber, [Name List].Inactive
FROM [Name List]
WHERE ((([Name List].PhoneNumber) In (SELECT [PhoneNumber] FROM [Name List]
As Tmp GROUP BY [PhoneNumber] HAVING Count(*)>1 And [PhoneNumber] = [Name
List].[PhoneNumber])) AND (([Name List].Inactive)=False))
ORDER BY [Name List].PhoneNumber;