query criteria and nulls

[mitchem1]

I have a query that is the source behind a Search form (frmFindProject). The form contains 5 unbound combo boxes: cboContractNumber, cboCounty, cboRoute, cboEngineer, and cboImprovement Type. The idea is for the user to retrieve records from a project table based on what is entered in the combos. For example, if the user enters nothing at all, all records should be retrieved. The criteria fields in the query look similar to this:

IIf([Forms]![frmFindProject]![ContractNumber] Is Null,[tblProject]![ContractNumber],[Forms]![frmFindProject]![cboContractNumber])

IIf([Forms]![frmFindProject]![cboCounty] Is Null,[tblProject]![CountyID],[Forms]![frmFindProject]![cboCounty])

and so on....

It works great, unless a record contains a null value in one of these columns. Then the record is not retrieved. I want to return all records, even those that contain nulls. Any advice would be greatly appreciated.

 

[DJN]

This criteria returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter, as it is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*") records with null values are returned.

Like IIf(IsNull(Forms!FormName![ControlName]), _ "*",[Forms]![FormName]![ControlName])

David

 

[mitchem1]

DJN,
Thanks for the reply.
I'm not sure I understand what the underscore preceding the asterisk is for.

Like IIf(IsNull(Forms!FormName![ControlName]), _ "*",[Forms]![FormName]![ControlName])

When I pasted in your code exactly as is (replacing FormName and ControlName), I received an error about expression too complex. When I tried the code without the underscore, I still didn't return records that contained nulls.

Not a lot of SQL experience, thanks for your patience.

 

[RV]

Post your SQL Statement (or attach it in a txt-file).

RV

 

[mitchem1]

SELECT tblProject.ContractNumber, tblProject.Section, tblProject.Location, tblResidentEngineer.ResidentEngineer, tblRoute.Route, tblCounty.County, tblImprovementType.ImprovementType

FROM tblRoute RIGHT JOIN (tblResidentEngineer RIGHT JOIN (tblImprovementType RIGHT JOIN (tblCounty RIGHT JOIN tblProject ON tblCounty.CountyID = tblProject.CountyID) ON tblImprovementType.ImprovementTypeID = tblProject.ImprovementTypeID) ON tblResidentEngineer.ResidentEngineerID = tblProject.ResidentEngineerID) ON tblRoute.RouteID = tblProject.RouteID

WHERE (((tblProject.ContractNumber) Like IIf(IsNull([Forms]![frmFindProject]![cboContractNumber]),"*",[Forms]![frmFindProject]![cboContractNumber])) AND ((tblProject.Section) Like IIf(IsNull([Forms]![frmFindProject]![cboSection]),"*",[Forms]![frmFindProject]![cboSection])) AND ((tblProject.Location) Like IIf(IsNull([Forms]![frmFindProject]![cboLocation]),"*",[Forms]![frmFindProject]![cboLocation])) AND ((tblProject.CountyID) Like IIf(IsNull([Forms]![frmFindProject]![cboCounty]),"*",[Forms]![frmFindProject]![cboCounty])) AND ((tblProject.RouteID) Like IIf(IsNull([Forms]![frmFindProject]![cboRoute]),"*",[Forms]![frmFindProject]![cboRoute])) AND ((tblProject.ImprovementTypeID) Like IIf(IsNull([Forms]![frmFindProject]![cboImprovementType]),"*",[Forms]![frmFindProject]![cboImprovementType])) AND ((tblProject.ResidentEngineerID) Like IIf(IsNull([Forms]![frmFindProject]![cboResidentEngineer]),"*",[Forms]![frmFindProject]![cboResidentEngineer])));

 

[mitchem1]

Pat, thanks for your reply.
My query still will not retrieve a record that has a null. I have a very small test table with 10 records. There are 7 columns:

ContractNumber
CountyID
RouteID
Section
Location
ImprovementTypeID
ResidentEngineerID

One record has a null in the ResidentEngineerID column. My query does not retrieve that record. My current SQL looks like this. Thank you very much.

SELECT tblProject.ContractNumber, tblProject.Section, tblProject.Location, tblResidentEngineer.ResidentEngineer, tblRoute.Route, tblCounty.County, tblImprovementType.ImprovementType
FROM tblRoute RIGHT JOIN (tblResidentEngineer RIGHT JOIN (tblImprovementType RIGHT JOIN (tblCounty RIGHT JOIN tblProject ON tblCounty.CountyID = tblProject.CountyID) ON tblImprovementType.ImprovementTypeID = tblProject.ImprovementTypeID) ON tblResidentEngineer.ResidentEngineerID = tblProject.ResidentEngineerID) ON tblRoute.RouteID = tblProject.RouteID
WHERE (((tblProject.ContractNumber) Like [Forms]![frmFindProject]![cboContractNumber] & "*") AND ((tblProject.Section) Like [Forms]![frmFindProject]![cboSection] & "*") AND ((tblProject.Location) Like [Forms]![frmFindProject]![cboLocation] & "*") AND ((tblProject.CountyID) Like [Forms]![frmFindProject]![cboCounty] & "*") AND ((tblProject.RouteID) Like [Forms]![frmFindProject]![cboRoute] & "*") AND ((tblProject.ImprovementTypeID) Like [Forms]![frmFindProject]![cboImprovementType] & "*") AND ((tblProject.ResidentEngineerID) Like [Forms]![frmFindProject]![cboResidentEngineer] & "*"));

 

[RV]

Try this:

WHERE (tblProject.ContractNumber
Like [Forms]![frmFindProject]![cboContractNumber]
OR [Forms]![frmFindProject]![cboContractNumber] Is Null)
AND next condition

RV