Query doesn't return record with null field

[puakenikeni]

I'm using a query as the source of my subform. It queries based on what is selected from four comboboxes. The query will display almost all of the records--except for the records that have a null value in one of the fields. There are a total of 449 records in my database. Out of the 449, 152 records have a null value in the "Disposition" field, so without any criteria chosen, the subform (as well as the query) will only show 298 records.

This is what my query looks like:

SELECT Master.ProgramName, Master.[Item Description], Master.ItemClass, Master.PropertyClass, Master.AcquiredFrom, Master.Gtag, Master.ItemClass, Master.ManufacturerVendor, Master.ModelNumber, Master.SerialNumber, Master.PartNumber, Master.Quantity, Master.UnitCost, Master.CurrentLocation, Master.Disposition, Master.DispositionCloseout, Master.DispositionLocation, Master.DispositionCloseoutDate, Master.DispositionComment
FROM Master
WHERE (((Master.ProgramName) Like "*" & [Forms]![frmInventory]![cboProgramName_tab2] & "*") AND ((Master.ItemClass) Like "*" & [Forms]![frmInventory]![cboItemClass_tab2] & "*") AND ((Master.PropertyClass) Like "*" & [Forms]![frmInventory]![cboPropertyClass_tab2] & "*") AND ((Master.Disposition) Like "*" & [Forms]![frmInventory]![cboDisposition_tab2] & "*"));

Is there a way I can change the query to also bring up all records, even with null fields? Is there a way I can approach this in the query portion, or do I need to do it through VBA on the form?

 

[Pat Hartman]

If you want to include null values, you must do so explictly. Do you want them included ONLY when no criteria is specified for a field?

 

[doco]

SELECT 
    Master.ProgramName, 
    Master.[Item Description], 
    Master.ItemClass, 
    Master.PropertyClass, 
    Master.AcquiredFrom, 
    Master.Gtag, 
    Master.ItemClass, 
    Master.ManufacturerVendor, 
    Master.ModelNumber, 
    Master.SerialNumber, 
    Master.PartNumber, 
    Master.Quantity, 
    Master.UnitCost, 
    Master.CurrentLocation, 
    Master.Disposition, 
    Master.DispositionCloseout, 
    Master.DispositionLocation, 
    Master.DispositionCloseoutDate, 
    Master.DispositionComment
FROM Master
WHERE 
    (((Master.ProgramName) Like "*" & 
        [Forms]![frmInventory]![cboProgramName_tab2] & "*") 
AND ((Master.ItemClass) Like "*" & 
        [Forms]![frmInventory]![cboItemClass_tab2] & "*") 
AND ((Master.PropertyClass) Like "*" & 
        [Forms]![frmInventory]![cboPropertyClass_tab2] & "*") 
AND ((Master.Disposition) Like "*" & 
        [Forms]![frmInventory]![cboDisposition_tab2] & "*"));

Now we can read your script. Which field is it you only want null values?

 

[namliam]

I agree with Doc, dont just splurge your SQL on the forum atleast make it readable, like he did.

Now for your problem, 2 ways your question can be interperted... but mostlikely you mean when you dont enter a value on your form you want "no filter" on the query.

You can do this by:
( (... Like "*FormControl*") or FormControl is null )

Tho this solution is less than optimal, it works. The best way is you build your query dynamicaly in SQL because using your "*FormControl*" will, if you are searching for "*xy*", it will return xy, xxyy and xxxyyy and you may or do not want that.
Also you are searching always with wildcards, what happens if you want an exact match?

This is why you need to do you query in code, so you can make it do exactly what you want.