?
Solved

Query to show only IS NOT NULL

Posted on 2008-02-07
4
Medium Priority
?
1,356 Views
Last Modified: 2011-08-18
When I create the query in Access, in the design mode, I am using IS NOT NULL in the criteria area. However, it isn't working at all. I am still getting everything.
SELECT UI_ISH_PROD_DB_sales_contact.cust_acct_nbr, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_area_cd, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr
FROM UI_ISH_PROD_DB_sales_contact
GROUP BY UI_ISH_PROD_DB_sales_contact.cust_acct_nbr, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_area_cd, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr
HAVING (((UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr) Is Not Null));

Open in new window

0
Comment
Question by:ba_trainer
  • 2
4 Comments
 
LVL 23

Expert Comment

by:Ashish Patel
ID: 20840508
Try this


SELECT UI_ISH_PROD_DB_sales_contact.cust_acct_nbr, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_area_cd, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr
FROM UI_ISH_PROD_DB_sales_contact
GROUP BY UI_ISH_PROD_DB_sales_contact.cust_acct_nbr, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_area_cd, UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr
HAVING IFNULL(UI_ISH_PROD_DB_sales_contact.prim_bus_ph_nbr, '') <> '';
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20840572
SELECT A.cust_acct_nbr, A.prim_bus_ph_area_cd, A.prim_bus_ph_nbr
FROM UI_ISH_PROD_DB_sales_contact as A
where A.prim_bus_ph_nbr Is Not Null
GROUP BY A.cust_acct_nbr, A.prim_bus_ph_area_cd, A.prim_bus_ph_nbr
0
 

Author Comment

by:ba_trainer
ID: 20841715
asvforce: - I get an error message regarding IFNULL

peter57r: - It doesn't change anything except for the table name.
0
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 20842632
Then you don't really have any nulls in your data.  The fields must contain something -  spaces probably.
I suggest you run this query to see if you have any nulls...

SELECT A.cust_acct_nbr, A.prim_bus_ph_area_cd, A.prim_bus_ph_nbr
FROM UI_ISH_PROD_DB_sales_contact as A
where A.prim_bus_ph_nbr Is Null
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

599 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