using forms control as report variable

[Seven]

I am trying to run a report using a form's control value as a query variable but cannot seem to get it to work. Specifically, I need to be able to return all of the records if the forms control is empty, but get no records.

this is not exact as I dont have access at this computer, but hopefully close enough for someone to help.



query

Iif([Forms]![MyForm]![MyControl],NULL,[Forms]![MyForm]![MyControl])

I thought it would look like this in the query...

if the control has a value, then use the value of the control as the query parameter (and only return the records that match the control's value), otherwise, use NULL as the query parameter (meaning to return all of the records, regardless of the control's value)


any help here is greatly appreciated.

[SoftwareMatters]

Add a new column in your report recordsource and enter this ([FIELD NAME] needs to be replaced with the field name in your db you are filtering against):

IIf(IsNull([forms]![MyForm]![MyControl]),([FIELD NAME]) Like "*" Or ([FIELD NAME]) Is Null,([FIELD NAME])=[forms]![MyForm]![MyControl])

Then in the criteria put:

<> False

[Seven]

thanks a million. I will check this out at work on monday!

[Seven]

Excellent! thanks again for the assist!