'IIf(IsNull(' in query not working.

[Samson]

I have a report driven by a query. The query is called by a form that captures certain criteria for the query.



I would like the query to ascertain whether the criteria entered is Null and if so replace the criteria in the query with the 'Like "*"' command.

This is the line from the query:
IIf(IsNull([Forms]![frmCardPaymentReportCriteria]![ReferenceType]),([tblCardPayment].[ReferenceType]) Like "*",[Forms]![frmCardPaymentReportCriteria]![ReferenceType])

For the life of me I cannot get this to work. If the form criteria contains data the report works as expected, but if it is Null I see no records at all.

The '[frmCardPaymentReportCriteria]![ReferenceType]' is a ComboBox which is set to Null in the Form-Load event of the criteria collection form.

I have also used a Text Box on the form which is set to Null in the Form_Load event and not touched as the value from the form in the 'IIf' statement and it still does not work.
IIf(IsNull([Forms]![frmCardPaymentReportCriteria]![TestText]),([tblCardPayment].[ReferenceType]) Like "*",[Forms]![frmCardPaymentReportCriteria]![ReferenceType])

I have also tried to use a Check Box on the form which looks like this in the query:
IIf([Forms]![frmCardPaymentReportCriteria]![chkAll],([tblCardPayment].[ReferenceType]) Like "*",[Forms]![frmCardPaymentReportCriteria]![ReferenceType])

I'm racking my brains here as I've spent most of the day trying work out where I've gone wrong but I can't see my mistake. Any help would be very much appreciated.

Thanks.

[Minty]

Remove the IIf() function completely in the criteria and use

= [Forms]![frmCardPaymentReportCriteria]![ReferenceType] or [Forms]![frmCardPaymentReportCriteria]![ReferenceType]  Is Null

This will automatically evaluate the second clause to true if the control is null, and return all the records, and is much more efficient than using the wildcard.
Note the space between the Is Null - it's a different expression to the vba function IsNull()

[mike60smart]

Hi
I would think that if your Control ReferenceType is Null you would have to specify a specific value to replace it with and
not use a Wildcard

[Samson]

Absolutely fantastic. Worked first time. You're a genius. Thanks.