Show Empty Records also on Query

[anishkgt]

Hi,

i've got a query that should also show empty records also. Tried using Or is null but that is not helping. The query has several tables also, is subquery an option here ? not sure how to do that either but is there another way to get the result.

thanks

 

[JHB]

A left join could maybe do it.

 

[anishkgt]

ok ! now how can i verify that ? sorry not so familiar with VBA, i mostly work with macro.

 

[anishkgt]

Anybody

 

[arnelgp]

JHB has the answer. can you post your query string (sql).

 

[anishkgt]

The SQL view of the query is as below

SELECT IIf([tblDelivery]![DelID],"Delivered","In Stock") AS [Delivered/In Stock], tblDelivery.DelID, tblDelivery.TimeOfDelivery, tblDelivery.EmployeeID_FK, tblPCC.AgencyName, tblCustomer.CustomerName, tblInventoryType.InventoryType, tblBrand.Brand, tblModel.Model, tblinventoryDetail.SerialNumber, tblCapex.Capex, tblCapex.Asset

FROM tblEmployee RIGHT JOIN (tblCustomer RIGHT JOIN (tblPCC RIGHT JOIN (tblInventoryType INNER JOIN (tblModel INNER JOIN (tblBrand INNER JOIN (tblCapex INNER JOIN (tblinventoryDetail LEFT JOIN tblDelivery ON tblinventoryDetail.InvID = tblDelivery.[InvID-FK]) ON tblCapex.CapexID = tblinventoryDetail.CapexID_FK) ON tblBrand.BrandID = tblinventoryDetail.BrandID_FK) ON tblModel.ModelID = tblinventoryDetail.ModelID_FK) ON tblInventoryType.InvTypeID = tblinventoryDetail.InvTypeID_FK) ON tblPCC.PCCID = tblDelivery.PCCID_FK) ON tblCustomer.CustomerID = tblDelivery.CustomerID_FK) ON tblEmployee.EmployeeID = tblDelivery.EmployeeID_FK

WHERE (((tblPCC.AgencyName) Like "*" & [Forms]![frmSearchConsolidated]![cmbAgencyName] & "*" Or (tblPCC.AgencyName) Is Null) AND ((tblCustomer.CustomerName) Like "*" & [Forms]![frmSearchConsolidated]![txtCustomerName] & "*" Or (tblCustomer.CustomerName) Is Null) AND ((tblInventoryType.InventoryType) Like "*" & [Forms]![frmSearchConsolidated]![txtInventoryType] & "*") AND ((tblBrand.Brand) Like "*" & [Forms]![frmSearchConsolidated]![txtBrand] & "*") AND ((tblModel.Model) Like "*" & [Forms]![frmSearchConsolidated]![txtModel] & "*") AND ((tblinventoryDetail.SerialNumber) Like [Forms]![frmSearchConsolidated]![txtSerialNumber]) AND ((tblCapex.Capex) Like [Forms]![frmSearchConsolidated]![txtCapex]));

 

[arnelgp]

make a copy of your original query. save it to something like [copy of yourquery]. edit your original query. replace all inner joins with Outer Left Join. see what happens.

 

[anishkgt]

I guess got the join all wrong, how does this look

SELECT IIf([qryDeliveryOverView]![DelID],"Delivered","In Stock") AS [Delivered/In Stock], qryDeliveryOverview.TimeOfDelivery, IIf([qryDeliveryOverview]![EmployeeName],[qrydeliveryoverview]![EmployeeName],"N/A") AS empName, IIf([qryDeliveryOverview]![AgencyName],[qryDeliveryOverview]![AgencyName],"N/A") AS DeliveredTo, IIf([qryDeliveryOverview]![CustomerName],[qryDeliveryOverview]![CustomerName],"N/A") AS Customer, qryInventoryDetail_ForView.InventoryType, qryInventoryDetail_ForView.Brand, qryInventoryDetail_ForView.Model, qryInventoryDetail_ForView.SerialNumber, qryInventoryDetail_ForView.Capex, qryInventoryDetail_ForView.Asset, qryInventoryDetail_ForView.Cost, qryInventoryDetail_ForView.InvID, qryDeliveryOverview.InvID

FROM qryInventoryDetail_ForView LEFT JOIN qryDeliveryOverview ON qryInventoryDetail_ForView.InvID = qryDeliveryOverview.InvID;

Attached here is the screenshot of the query. The idea here with the query is to display the result of a search criteria to show if the particular record is delivered or in stock.

 

[anishkgt]

Some results are based on different tables, for example AgencyName is not available in tblInventoryDetail as its not delivered and it shows up in tblDelivery. So the result show blank for AgencyName if the query searched with SerialNumber. The serial number is always there in tblInventoryDetail and is not in tblDelivery if not delivered.

 

[anishkgt]

All these work when these are are added into the criteria as "4552015" or others. So frustrating:banghead: