IIF statement that uses like and also bring back null values

[mbair]

I am using this IIF statement on a field called department.
Like IIf(IsNull([Forms]![frmTrainingHoursLess40]![cboDepartment]),"*",[Forms]![frmTrainingHoursLess40]![cboDepartment])
but I also need it to return the records even if the deparment field is null.

 

[lameid]

I've never had much luck passing a wildcard in any such fashion within a query. Is there any chance you could just put the information on a report and pass it a filter...

stDocName = "ReportName"
stLinkCriteria = "Department Like """ & IIf(IsNull([Forms]![frmTrainingHoursLess40]![cboDepartment]),"*",[Forms]![frmTrainingHoursLess40]![cboDepartment]) & """"

DoCmd.OpenReport stDocName, acPreview,,stLinkCriteria

That would be the easy way.

Alternately, you could change the SQL of the query or relevent form property. I guess this leads to 2 questions.

1 What is the sql of your query?
2 How is the query used? Maybe we can set the Rowsource of the combo box to a literal sql statement, or maybe we actually have to change an sql property.

 

[Zion7]

Not sure if I understand, your objective but, maybe this,
strCriteria = IIf(IsNull([Forms]![frmTrainingHoursLess40]![cboDepartment]),"*",[Forms]![frmTrainingHoursLess40]![cboDepartment])

Me.Department = "Like '" & strCriteria & "'

 

[PHV]

Something like this in the criteria cell ?
Like [Forms]![frmTrainingHoursLess40]![cboDepartment] OR [Forms]![frmTrainingHoursLess40]![cboDepartment] Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

PHV is thinking about SQL and the where condition and not the QBE (query design view). Although, is suggestion should work...

He means have
Like [Forms]![frmTrainingHoursLess40]![cboDepartment]
in the cell you have been talking about and add another column for [Forms]![frmTrainingHoursLess40]![cboDepartment]
Under this new column in the second criteria line, put
Is Null
In the second line you will likely want to duplicate everything from the first line except for the
Like [Forms]![frmTrainingHoursLess40]![cboDepartment]

 

[PHV]

lameid, I thought about the criteria cell in the query grid, on one line.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lameid]

PHV,

My apologies. I really didn't think that would work. I assumed incorrectly that the QBE forced the test against the field above. At least I would have gotten there the long way <sigh>.