Combobox Query Ignoring Blank Fields

[mjoachim]

I currently have a Combobox that is linked to a table and is designed to limit possible entries based on 2 other form fields. To do this, I am using this query:

SELECT [Cost Codes].CostCode 
FROM [Cost Codes] 
WHERE ((([Cost Codes].JobNumber)
        =[Forms]![Data Import]![Job Number]) 
    AND (([Cost Codes].SubJobNumber)
        =[Forms]![Data Import]![Sub Job])) 
ORDER BY [Cost Codes].CostCode;

The problem I'm having is that if the Sub Job on the Form is left blank, the query isn't returning anything, rather than the desired filtering to table entries with a matched job number and blank sub job number.

I have tried playing around with Is Null and if placed in the Or portion of the Query Builder, table entries with blank Sub Jobs always appear in the ComboBox.

Any ideas on how to modify this to only show Cost Codes with matching Job and Sub Job numbers, even if the Sub Job field is blank?

[jforbes]

Typically for something like this, you can add a test for Null/Blank value for the SubJob as part of the Criteria.

SELECT [Cost Codes].CostCode FROM [Cost Codes]
WHERE [Cost Codes].JobNumber)=[Forms]![Data Import]![Job Number] 
AND ([Cost Codes].SubJobNumber)=[Forms]![Data Import]![Sub Job] OR Nz([Forms]![Data Import]![Sub Job], '')='')
ORDER BY [Cost Codes].CostCode;

The Null/Blank test is ORed with the test for a specific SubJob so either a specific value is returned or all values are returned.

[mjoachim]

You are correct that attaching the Nz statement on the end does provide all values if the field is left blank.

To clarify what I need though: a blank field is essentially also a value. When the field is left blank, I can't have Cost Code values that belong to Sub Job 100 or 200.

Hopefully that makes more sense now, sorry for the confusion!

[zmbd]

Essentially, the blank field is not a value which is why the NZ() behaved the way it did for your results.
(MSACC/VBA Insights> What is Null? )

As for what you are wanting for results, I think you need to provide some example data...
Just click on the [CODE/] button in the post toolbar and insert your tabular raw data and your desired results between the sets of "code tags"

You should also look at:
MSACC/VBA Insights> Cascaded Form Filtering - sounds like this is what you are attempting....

[mjoachim]

JobNumber SubJobNumber       CostCode
132401                 0000.000000        
132401                 0100.010100        
132401          200     0100.010800        
132401          200     0300.030700        
151601          100     0400.040100        
151601          100     0400.040200        
151601                 0400.040200        
151601          100     0400.040225        
151601          200     0600.060100        
151601                 0600.060100        
151601          200     0600.060180        
152405          100     1000.100300        
152405                 1000.100400        
152405                 1100.110100        
152405          200     1100.110100        
152405          100     1100.110200        
152405                 1100.110200        
152405                 1100.110250        
 

The form has a control for Job Number, Sub Job and Cost Code. Selections for Job Number and Sub Job should limit the combo box options for Cost Code. A Sub Job value of blank should be treated as it's own value unique from other Sub Job's so that if the control is left blank, Cost Codes that belong to Job Number 151601 & Sub Job 100 are not available in the combo box.

[mjoachim]

jforbes,

To build off your idea, rather than just assigning the form Nz values to "", I also assigned the table Nz values to "" which appears to have worked to provide them with a matchable value.

SELECT [Cost Codes].CostCode
FROM [Cost Codes]
WHERE ((([Cost Codes].SubJobNumber)=[Forms]![Data Import]![Sub Job]) AND (([Cost Codes].JobNumber)=[Forms]![Data Import]![Job Number])) OR ((([Cost Codes].JobNumber)=[Forms]![Data Import]![Job Number]) AND ((Nz([Cost Codes].[SubJobNumber],""))=Nz([Forms]![Data Import]![Sub Job],"")))
ORDER BY [Cost Codes].CostCode;
 

Thank you for everyone's help!

[zmbd]

Three controls feeding from each other... follow the cascading filter example.

Test for isnull
WHERE [tbl]![field] is null
or
WHERE Not([tbl]![field]) is null