Query to show records based on input field if another field is blank or has a value.

[sheusz]

Hello Brains trust.

I've been struggling with this query that is used to generate a report. I think I need to use the IIF statement to get my desired result, but am unsure how.

SELECT SoftwareKeys.OrderNo, SoftwareKeys.PurchaseDate, SoftwareKeys.SoftwareName, SoftwareKeys.KeyNo, SoftwareKeyCodes.SoftwareLevel, SoftwareKeyCodes.SoftwareLevelCode, SoftwareKeyCodes.Comment, SoftwareKeys.CustomerID
FROM Customers INNER JOIN (SoftwareKeys INNER JOIN SoftwareKeyCodes ON SoftwareKeys.KeyNo = SoftwareKeyCodes.KeyNo) ON Customers.CustomerID = SoftwareKeys.CustomerID
WHERE (((SoftwareKeys.OrderNo) Like "*" & [Enter the purchase order number:] & "*"));

As it is now, the query returns only those records where the SoftwareKeys.OrderNo has an entry in the SoftwareKeys.CustomerID field. (i.e not Null)

What I need the query to do is show ALL the records for SoftwareKeys.OrderNo, regardless of whether SoftwareKeys.CustomerID is Null of not.

Thanks in advance for your time and input.

[Bob Fitz]

Have you tried:

SELECT SoftwareKeys.OrderNo, SoftwareKeys.PurchaseDate, SoftwareKeys.SoftwareName, SoftwareKeys.KeyNo, SoftwareKeyCodes.SoftwareLevel, SoftwareKeyCodes.SoftwareLevelCode, SoftwareKeyCodes.Comment, SoftwareKeys.CustomerIDFROM Customers INNER JOIN (SoftwareKeys INNER JOIN SoftwareKeyCodes ON SoftwareKeys.KeyNo = SoftwareKeyCodes.KeyNo) ON Customers.CustomerID = SoftwareKeys.CustomerID ;

[Micron]

You will need to change one or both of your joins, methinks. An INNER join can only return records where both fields are equal, and nothing equals Null. You want ALL the records from one table, and from the other table, whatever records match between the joined fields. Double click on the joins and read the explanations for the 3 options.

What I don't get is how you can have a software key value in an orders table but no related customer id.

[sheusz]

Hi Micron and Bob.

You will need to change one or both of your joins, methinks. An INNER join can only return records where both fields are equal, and nothing equals Null. You want ALL the records from one table, and from the other table, whatever records match between the joined fields. Double click on the joins and read the explanations for the 3 options.

What I don't get is how you can have a software key value in an orders table but no related customer id.

Thank you very much for your replies. I have solved the issue using your suggestions.

Please see new code below;

SELECT SoftwareKeys.OrderNo, SoftwareKeys.PurchaseDate, SoftwareKeys.SoftwareName, SoftwareKeys.KeyNo, SoftwareKeyCodes.SoftwareLevel, SoftwareKeyCodes.SoftwareLevelCode, SoftwareKeyCodes.Comment, SoftwareKeys.CustomerID, Customers.CompanyName
FROM (Customers RIGHT JOIN SoftwareKeys ON Customers.CustomerID = SoftwareKeys.CustomerID) INNER JOIN SoftwareKeyCodes ON SoftwareKeys.KeyNo = SoftwareKeyCodes.KeyNo
WHERE (((SoftwareKeys.OrderNo) Like "*" & [Enter the purchase order number:] & "*"));

Micorn, Bad practice I know, but there is no Orders table. The OrderNo is stored with KeyNo record.

[Micron]

Micorn, Bad practice I know, but there is no Orders table

Well I suppose we will be 'seeing' you later!
Regards,
Micron