Query with blank Values

[chrisguk]

Hi there,

I put the query below together but when i run it, it returns nothing. I know some of the field values are blank but I still want it to return the data that is present. Any ideas?

SELECT tblSitesHost.HostDetail, tblSites.SiteCode, tblSites.SiteName, tblAddress.Province, tblAddress.City, tblSitesStato.StatoDetail, tblSitesMeterProgram.dteProgrammedDetail, tblSitesMeters.dteInstall, IIf([dteInstall]>0,"1","0") AS Consuntivo, tblSitesClientNotes.ClientNotes
FROM (((tblSitesStato INNER JOIN (tblSitesHost INNER JOIN tblSites ON tblSitesHost.HostID = tblSites.fkHostID) ON tblSitesStato.pkStatoID = tblSites.fkStatoID) INNER JOIN tblAddress ON tblSites.pkSitesID = tblAddress.fkSitesID) INNER JOIN tblSitesClientNotes ON tblSites.pkSitesID = tblSitesClientNotes.fkSitesID) INNER JOIN (tblSitesMeters INNER JOIN tblSitesMeterProgram ON tblSitesMeters.MeterID = tblSitesMeterProgram.fkMeterID) ON tblSites.pkSitesID = tblSitesMeters.fkSitesID
WHERE (((tblSites.SiteName) Is Null) AND ((tblAddress.Province) Is Null) AND ((tblAddress.City) Is Null) AND ((tblSitesStato.StatoDetail) Is Null) AND ((tblSitesMeterProgram.dteProgrammedDetail) Is Null) AND ((tblSitesMeters.dteInstall) Is Null) AND ((IIf([dteInstall]>0,"1","0")) Is Null) AND ((tblSitesClientNotes.ClientNotes) Is Null));

 

[Alansidman]

By using 'AND' in your WHERE clause you are asking Access to give you only records where all the fields identified are Null. Is this really what you want or do you want records where at least one of those fields is Null. If the latter, replace the 'AND' in your WHERE clause with 'OR.'

 

[chrisguk]

Ok I did that and still nothing. I have posted my query design below:

Uploaded with ImageShack.us

 

[Alansidman]

Try changing your join from an inner join from your TblSites to an outer join from TblSites. You will need to set it up as a LEFT or RIGHT join (your preference) but be consistent.

This may help you to understand the principle.

http://office.microsoft.com/en-us/access-help/join-tables-and-queries-HA010096320.aspx

Alan