Solved Split query (1 Viewer)

[Kayleigh]

Hi,
I have a dilemma regarding writing SQL for a query.
I am attempting to find all records which contain NULL values for the 'required' fields. Complication arises where one field may be null if another field has value 2. Have tried various combinations of AND/OR statements, as well as an IIF expression but can't get the desired results.
Thinking that it may be best to be done as two queries but not sure how to split it.
See current SQL below.

SELECT tblClients.fldClientID, tblAddress.fldAddressID, tblOrders.fldOrderID, IIf(([fldCTradeRetailID]<>2) And (IsNull([fldCBusiness])),"Yes","No") AS Validate, tblClients.fldCFirstName, tblClients.fldCLastName, tblClients.fldCAddress1, tblClients.fldCStreet, tblClients.fldCCity, tblClients.fldCPostcode, tblClients.fldCPhone1, tblClients.fldCEmail1, tblClients.fldCHDYHAUID, tblAddress.fldAAddress1, tblAddress.fldAStreet, tblAddress.fldACity, tblAddress.fldAPostcode, tblOrders.fldOSupplyFitID, tblOrders.fldOCompanyID, tblOrders.fldOJobDescription, tblOrders.fldOSurveyYN, tblOrders.fldOStatusID
FROM tblClients INNER JOIN (tblAddress INNER JOIN tblOrders ON tblAddress.fldAddressID = tblOrders.fldOAddressID) ON tblClients.fldClientID = tblAddress.fldAClientID
WHERE (((IIf(([fldCTradeRetailID]<>2) And (IsNull([fldCBusiness])),"Yes","No"))=Yes) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCFirstName) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCLastName) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCAddress1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCStreet) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCCity) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCPostcode) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCPhone1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCEmail1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCHDYHAUID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAAddress1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAStreet) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldACity) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAPostcode) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOSupplyFitID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOCompanyID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOJobDescription) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOSurveyYN) Is Null) AND ((tblOrders.fldOStatusID)=10));

 

[plog]

Your written explanation is as ambigous as your code--does one Null value qualify it for the query? Or must every field be Null? Where did this field value equal 2 come from? And then your code has something about an ID field being 10. Hard to follow.

The best way to communicate data issues is with 2 sets of data

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.



B. Expected results of A. Show what data you expect to end up with when you feed your query the data from A.



Again, 2 sets of data that tie together: starting and expected results.

 

[Kayleigh]

So actually managed to achieve required results with 3 queries (!)
1 - Found all records with orderStatus = 10 and some null value
2 - All records which are trade value '2' and may have null value for business field (as per business rules) - these I DO NOT WANT
3 - Ran query wizard unmatched query to find all records which are in #1 but NOT in #2

Wonder if there is a better way to do this?

Here is SQL of first two queries (third is just excluding unnecessary data):

SELECT tblOrders.fldOrderID, tblAddress.fldAClientID, tblOrders.fldOAddressID, tblOrders.fldOCreated, tblClients.fldCTradeRetailID, tblOrders.fldOContactDate, tblOrders.fldOContactMade, tblOrders.fldOFollowupStatusID, tblOrders.fldOJobTypeID, tblOrders.fldONotes, tblClients.fldCBusiness, tblClients.fldCFirstName, tblClients.fldCLastName, tblClients.fldCAddress1, tblClients.fldCStreet, tblClients.fldCCity, tblClients.fldCPostcode, tblClients.fldCPhone1, tblClients.fldCEmail1, tblClients.fldCHDYHAUID, tblAddress.fldAAddress1, tblAddress.fldAStreet, tblAddress.fldACity, tblAddress.fldAPostcode, tblOrders.fldOSupplyFitID, tblOrders.fldOCompanyID, tblOrders.fldOJobDescription, tblOrders.fldOSurveyYN, tblOrders.fldOStatusID
FROM tblClients INNER JOIN (tblAddress INNER JOIN tblOrders ON tblAddress.fldAddressID = tblOrders.fldOAddressID) ON tblClients.fldClientID = tblAddress.fldAClientID
WHERE (((tblClients.fldCTradeRetailID)=2) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCFirstName) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCLastName) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCAddress1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCStreet) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCCity) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCPostcode) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCPhone1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCEmail1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblClients.fldCHDYHAUID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAAddress1) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAStreet) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldACity) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblAddress.fldAPostcode) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOSupplyFitID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOCompanyID) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOJobDescription) Is Null) AND ((tblOrders.fldOStatusID)=10)) OR (((tblOrders.fldOSurveyYN) Is Null) AND ((tblOrders.fldOStatusID)=10));

SELECT qryShortEnquiriesSUB.fldOrderID, qryShortEnquiriesSUB.fldCTradeRetailID, qryShortEnquiriesSUB.fldCFirstName, qryShortEnquiriesSUB.fldCLastName, qryShortEnquiriesSUB.fldCAddress1, qryShortEnquiriesSUB.fldCStreet, qryShortEnquiriesSUB.fldCCity, qryShortEnquiriesSUB.fldCPostcode, qryShortEnquiriesSUB.fldCPhone1, qryShortEnquiriesSUB.fldCEmail1, qryShortEnquiriesSUB.fldCHDYHAUID, qryShortEnquiriesSUB.fldAAddress1, qryShortEnquiriesSUB.fldAStreet, qryShortEnquiriesSUB.fldACity, qryShortEnquiriesSUB.fldAPostcode, qryShortEnquiriesSUB.fldOSupplyFitID, qryShortEnquiriesSUB.fldOCompanyID, qryShortEnquiriesSUB.fldOJobDescription, qryShortEnquiriesSUB.fldOSurveyYN
FROM qryShortEnquiriesSUB
WHERE (((qryShortEnquiriesSUB.fldCTradeRetailID)=2) AND ((qryShortEnquiriesSUB.fldCFirstName) Is Not Null) AND ((qryShortEnquiriesSUB.fldCLastName) Is Not Null) AND ((qryShortEnquiriesSUB.fldCAddress1) Is Not Null) AND ((qryShortEnquiriesSUB.fldCStreet) Is Not Null) AND ((qryShortEnquiriesSUB.fldCCity) Is Not Null) AND ((qryShortEnquiriesSUB.fldCPostcode) Is Not Null) AND ((qryShortEnquiriesSUB.fldCPhone1) Is Not Null) AND ((qryShortEnquiriesSUB.fldCEmail1) Is Not Null) AND ((qryShortEnquiriesSUB.fldCHDYHAUID) Is Not Null) AND ((qryShortEnquiriesSUB.fldAAddress1) Is Not Null) AND ((qryShortEnquiriesSUB.fldAStreet) Is Not Null) AND ((qryShortEnquiriesSUB.fldACity) Is Not Null) AND ((qryShortEnquiriesSUB.fldAPostcode) Is Not Null) AND ((qryShortEnquiriesSUB.fldOSupplyFitID) Is Not Null) AND ((qryShortEnquiriesSUB.fldOCompanyID) Is Not Null) AND ((qryShortEnquiriesSUB.fldOJobDescription) Is Not Null) AND ((qryShortEnquiriesSUB.fldOSurveyYN) Is Not Null));

Happy to provide more data if this is not enough...

 

[jdraw]

SELECT qryShortEnquiriesSUB.fldOrderID
    ,qryShortEnquiriesSUB.fldCTradeRetailID
    ,qryShortEnquiriesSUB.fldCFirstName
    ,qryShortEnquiriesSUB.fldCLastName
    ,qryShortEnquiriesSUB.fldCAddress1
    ,qryShortEnquiriesSUB.fldCStreet
    ,qryShortEnquiriesSUB.fldCCity
    ,qryShortEnquiriesSUB.fldCPostcode
    ,qryShortEnquiriesSUB.fldCPhone1
    ,qryShortEnquiriesSUB.fldCEmail1
    ,qryShortEnquiriesSUB.fldCHDYHAUID
    ,qryShortEnquiriesSUB.fldAAddress1
    ,qryShortEnquiriesSUB.fldAStreet
    ,qryShortEnquiriesSUB.fldACity
    ,qryShortEnquiriesSUB.fldAPostcode
    ,qryShortEnquiriesSUB.fldOSupplyFitID
    ,qryShortEnquiriesSUB.fldOCompanyID
    ,qryShortEnquiriesSUB.fldOJobDescription
    ,qryShortEnquiriesSUB.fldOSurveyYN
FROM qryShortEnquiriesSUB
WHERE (
        ((qryShortEnquiriesSUB.fldCTradeRetailID) = 2)
        AND ((qryShortEnquiriesSUB.fldCFirstName) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCLastName) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCAddress1) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCStreet) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCCity) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCPostcode) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCPhone1) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCEmail1) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldCHDYHAUID) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldAAddress1) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldAStreet) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldACity) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldAPostcode) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldOSupplyFitID) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldOCompanyID) IS NOT NULL)
        AND ((qryShortEnquiriesSUB.fldOJobDescription) IS NOT NULL
            )
        AND ((qryShortEnquiriesSUB.fldOSurveyYN) IS NOT NULL)
        );

Wonder if there is a better way to do this?

Perhaps those fields could have been set up with Required=Yes. We don't know the details of your business nor environment.

As plog noted, your requirement is not clear.

Also there is a free SQL formatting tool --Poor SQL that will format your SQL and make it more readable when posting.
I intended the formatted code to go below this line, but it seems to have been placed at the start??

 

[arnelgp]

Wonder if there is a better way to do this?

the final quey based on small queries is the way to go.
be happy with your solution.

 

[Kayleigh]

great thanks!

My next question is how to apply this as a filter of an updateable form. When I try as part of the recordsource it doesn't allow changes to the recordset.
Is there any way to write a filter of the form to state: only include records (or orderID field) in final query above?

Tried this code but didn't do anything....

Private Sub Form_Current()
Dim strFilter As String
strFilter = "[OrderID] IN ([frmOrderEnquiry])"
Debug.Print strFilter '<- examine this in Immediate window; Ctrl+g will take you there
Me.Filter = strFilter
Me.FilterOn = True
End Sub

 

[arnelgp]

what is the rowsource of your form?
you will then need to create a Query that has "all" the fields (or just the fldOrderID) as with your form:

(qryFilter):
select fldOrderID from yourTable Where fldOrderID IN (select fldOrderID from theFinalQueryNameYoMade);


now to filter your form:

DoCmd.ApplyFilter "qryFilter"