Display query results only if field is not null

[TinaR]

I have a simple query based on two tables that are linked together. I'm
pulling the results based on a date. The first table is a current roster.
The second table is for changes to the roster. If the street address
changes, I enter the new street address in the appropriate street address
field of the changes table. Another record may have a different type of
change (facility name, purchasing manager, etc.).

When I run my query, I'm bringing back results based on the date the changes
are submitted. The query results show the old information (from the current
roster table) and the new information (from the changes table). So far so
good. What I'd like to do is show the old information from the current
roster, only if there is a change to that field. Currently, all the
information shows from the current roster table.

Is there a way to put a condition in the query that says to show the results
from the current roster, only if the corresponding changes field is not null?

Thanks,
Tina

 

[KARL DEWEY]

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.

 

[TinaR]

Here's the SQL...which I'm not familiar with:

SELECT tbChanges.GPOID, [ActiveHPG Roster].PriorCOID, tbChanges.NewCOID,
[ActiveHPG Roster].Facility, tbChanges.NewNameOfBusiness, [ActiveHPG
Roster].Investor, [ActiveHPG Roster].Company, [ActiveHPG Roster].Group,
tbChanges.NewGroup, [ActiveHPG Roster].Division, tbChanges.NewDivision,
tbChanges.PriorMarket, tbChanges.NewMarket, ([Address1]) & " " & ([Address2])
& " " & ([City]) & ", " & ([State]) & " " & ([Zip]) AS [Priori Street
Address], tbChanges.NewStreetAddress1, tbChanges.NewStreetAddress2,
tbChanges.NewCity, tbChanges.NewState, tbChanges.NewZip,
tbChanges.NewCountry, [ActiveHPG Roster].[Phone #], tbChanges.NewPhone,
tbChanges.PriorGLN, tbChanges.NewGLN, tbChanges.PriorHIN, tbChanges.NewHIN,
tbChanges.PrioConsortaID, tbChanges.NewConsortaID, tbChanges.TaxStatus,
[ActiveHPG Roster].[Licensed Beds], tbChanges.NewLicensecBeds, [ActiveHPG
Roster].[Class Of Trade], tbChanges.NewCOT, [ActiveHPG Roster].Type,
tbChanges.NewFacilityType, [ActiveHPG Roster].[MM Name],
tbChanges.NewPurchasingMgrFirstName, tbChanges.NewPurchasingMgrLastName,
tbChanges.NewPurchasingMgrStreeAddress1,
tbChanges.NewPurchasingMgrStreetAddress2, tbChanges.NewPurchasingMgrCity,
tbChanges.NewPurchasingMgrSt, tbChanges.NewPurchasingMgrZip,
tbChanges.NewPurchasingMgrCountry, tbChanges.NewPurchasingMgrPhone,
tbChanges.NewPurchasingMgrMobile, tbChanges.NewPurchasingMgrFax,
tbChanges.NewPurchasingMgrEMail, [ActiveHPG Roster].DEA, tbChanges.NewDEA,
tbChanges.DEAName, tbChanges.Comments, tbChanges.SentToHPG, tbChanges.Action,
tbChanges.Other
FROM [ActiveHPG Roster] LEFT JOIN tbChanges ON [ActiveHPG Roster].GPOID =
tbChanges.GPOID
WHERE (((tbChanges.SentToHPG)=[Enter Date Sent to HPG]));

 

[KARL DEWEY]

I only found 4 fields that corresponded.

Make a copy of the query. Paste this over your present WHERE statement and
try it --
WHERE (((tbChanges.SentToHPG)=[Enter Date Sent to HPG])) AND
(tbChanges.NewCOID+tbChanges.NewDEA+tbChanges.NewDivision+tbChanges.NewGroup)
Is Not Null;

--
Build a little, test a little.

Here's the SQL...which I'm not familiar with:

SELECT tbChanges.GPOID, [ActiveHPG Roster].PriorCOID, tbChanges.NewCOID,
[ActiveHPG Roster].Facility, tbChanges.NewNameOfBusiness, [ActiveHPG
Roster].Investor, [ActiveHPG Roster].Company, [ActiveHPG Roster].Group,
tbChanges.NewGroup, [ActiveHPG Roster].Division, tbChanges.NewDivision,
tbChanges.PriorMarket, tbChanges.NewMarket, ([Address1]) & " " & ([Address2])
& " " & ([City]) & ", " & ([State]) & " " & ([Zip]) AS [Priori Street
Address], tbChanges.NewStreetAddress1, tbChanges.NewStreetAddress2,
tbChanges.NewCity, tbChanges.NewState, tbChanges.NewZip,
tbChanges.NewCountry, [ActiveHPG Roster].[Phone #], tbChanges.NewPhone,
tbChanges.PriorGLN, tbChanges.NewGLN, tbChanges.PriorHIN, tbChanges.NewHIN,
tbChanges.PrioConsortaID, tbChanges.NewConsortaID, tbChanges.TaxStatus,
[ActiveHPG Roster].[Licensed Beds], tbChanges.NewLicensecBeds, [ActiveHPG
Roster].[Class Of Trade], tbChanges.NewCOT, [ActiveHPG Roster].Type,
tbChanges.NewFacilityType, [ActiveHPG Roster].[MM Name],
tbChanges.NewPurchasingMgrFirstName, tbChanges.NewPurchasingMgrLastName,
tbChanges.NewPurchasingMgrStreeAddress1,
tbChanges.NewPurchasingMgrStreetAddress2, tbChanges.NewPurchasingMgrCity,
tbChanges.NewPurchasingMgrSt, tbChanges.NewPurchasingMgrZip,
tbChanges.NewPurchasingMgrCountry, tbChanges.NewPurchasingMgrPhone,
tbChanges.NewPurchasingMgrMobile, tbChanges.NewPurchasingMgrFax,
tbChanges.NewPurchasingMgrEMail, [ActiveHPG Roster].DEA, tbChanges.NewDEA,
tbChanges.DEAName, tbChanges.Comments, tbChanges.SentToHPG, tbChanges.Action,
tbChanges.Other
FROM [ActiveHPG Roster] LEFT JOIN tbChanges ON [ActiveHPG Roster].GPOID =
tbChanges.GPOID
WHERE (((tbChanges.SentToHPG)=[Enter Date Sent to HPG]));

Post the SQL of your query by opening in design view, click on VIEW - SQL
View, highlight all, copy, and paste in a post.