Link to home
Start Free TrialLog in
Avatar of ba_trainer
ba_trainer

asked on

Query to show only IS NOT NULL

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

Avatar of Ashish Patel
Ashish Patel
Flag of India image

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, '') <> '';
Avatar of peter57r
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
Avatar of ba_trainer
ba_trainer

ASKER

asvforce: - I get an error message regarding IFNULL

peter57r: - It doesn't change anything except for the table name.
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial