CODEWITHSUNDEEP
sqlvbams-access
Stan S
Stan S

Reputation: 29

Select query returning no records/results if another filed is blank/null

I have a form with various fields used to filter a select query built on a single table. Problem is that some of the fields in the record (which are also potential criteria fields) are null/blank e.g."FirstName" & "LastName" may both be blank but there IS a "StudentID".

When entering a "StudentID" associated with a record that has all other fields null/blank the query does not return the record but rather returns no records at all. I would like the query to return all records that are associated with the particular criteria despite other fields being blank/null.

In the past, I have assigned a default value like "NoName..." on all new records so the field is not blank and that works but annoying...

I am having a brain fart... Please let me know if more info is needed. TYIA

SELECT 
    SAP_RECORD_T.FirstName, 
    SAP_RECORD_T.LastName, 
    SAP_RECORD_T.StudentID,
    SAP_RECORD_T.Term, 
    SAP_RECORD_T.FileComplete, 
    SAP_RECORD_T.CampusSubmitted
FROM SAP_RECORD_T
WHERE 
    (
        ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
        AND ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*") 
        AND ((SAP_RECORD_T.StudentID) Like "*" & [Forms]![SearchBox_F]![StudentID] & "*") 
        AND ((SAP_RECORD_T.Term) Like "*" & [Forms]![SearchBox_F]![txtTerm] & "*") 
        AND ((SAP_RECORD_T.FileComplete) Like "*" & [Forms]![SearchBox_F]![txtFileCmplt] & "*") 
        AND ((SAP_RECORD_T.CampusSubmitted) Like "*" & [Forms]![SearchBox_F]![txtCampus] & "*")
    ) OR (
        ((SAP_RECORD_T.FirstName) Is Null) 
        AND ((SAP_RECORD_T.LastName) Is Null) 
        AND ((SAP_RECORD_T.StudentID) Is Null) 
        AND ((SAP_RECORD_T.Term) Is Null) 
        AND ((SAP_RECORD_T.FileComplete) Is Null) 
        AND ((SAP_RECORD_T.CampusSubmitted) Is Null)
    );

Upvotes: 1

Views: 1538

Answers (2)

GMB
GMB

Reputation: 222432

You need to change the logic in the conditions of the WHERE clause.

Your WHERE clause :

WHERE 
    (
        ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
        AND ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*") 
        AND ((SAP_RECORD_T.StudentID) Like "*" & [Forms]![SearchBox_F]![StudentID] & "*") 
        AND ((SAP_RECORD_T.Term) Like "*" & [Forms]![SearchBox_F]![txtTerm] & "*") 
        AND ((SAP_RECORD_T.FileComplete) Like "*" & [Forms]![SearchBox_F]![txtFileCmplt] & "*") 
        AND ((SAP_RECORD_T.CampusSubmitted) Like "*" & [Forms]![SearchBox_F]![txtCampus] & "*")
    ) OR (
        ((SAP_RECORD_T.FirstName) Is Null) 
        AND ((SAP_RECORD_T.LastName) Is Null) 
        AND ((SAP_RECORD_T.StudentID) Is Null) 
        AND ((SAP_RECORD_T.Term) Is Null) 
        AND ((SAP_RECORD_T.FileComplete) Is Null) 
        AND ((SAP_RECORD_T.CampusSubmitted) Is Null)
    )

As it is, your conditions allow records that either match on all search box conditions, or where all filter columns are NULL. Instead, you want to check NULLity for each column individually before applying the filter.

New version :

WHERE 
    (
        ( 
            ((SAP_RECORD_T.FirstName) Is Null)  
            OR ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
        )
        AND (
            ((SAP_RECORD_T.LastName) Is Null) 
            OR ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*")  
        )
        AND (
            ((SAP_RECORD_T.StudentID) Is Null)
            OR ((SAP_RECORD_T.StudentID) Like "*" & [Forms]![SearchBox_F]![StudentID] & "*")
        ) 
        AND (
            ((SAP_RECORD_T.Term) Is Null) 
            OR ((SAP_RECORD_T.Term) Like "*" & [Forms]![SearchBox_F]![txtTerm] & "*") 
        )
        AND (
            ((SAP_RECORD_T.FileComplete) Is Null) 
            OR ((SAP_RECORD_T.FileComplete) Like "*" & [Forms]![SearchBox_F]![txtFileCmplt] & "*") 
        )
        AND (
            ((SAP_RECORD_T.CampusSubmitted) Is Null)
            OR ((SAP_RECORD_T.CampusSubmitted) Like "*" & [Forms]![SearchBox_F]![txtCampus] & "*")
        )
    )

NB : these things are easier to spot when the query is properly formated (indentation, ...).

Upvotes: 1

RiyaGeorge
RiyaGeorge

Reputation: 145

WHERE ((SAP_RECORD_T.FirstName) Like "*" & [Forms]![SearchBox_F]![txtFName] & "*") 
OR ((SAP_RECORD_T.FirstName) Is Null)
AND ((SAP_RECORD_T.LastName) Like "*" & [Forms]![SearchBox_F]![txtLName] & "*") 
OR((SAP_RECORD_T.LastName) Is Null)

Similarly, for other columns, check if the column matches criteria OR is null.

Upvotes: 0

Related Questions