Return null values in a Parameter query

[Gazzamuffin]

I have a parameter query that is determined by the date entered on a form. The field contains either a date or is blank.

If the form is left blank I need all data to be returned, if a date is entere I need only those greater than the date entered to be returned.

I have tried

IIf(isnull([Form]),[date],>[Form])

however if the form is left blank it only returns all those with dates and excludes the blank fields.

Can anyone help?

Cheers
G

 

[cmmrfrds]

If I understand correctly, you have data in a table where some of the dates are NULL and want to return the rows using the date as the criteria. You will probably need an OR condition to catch the nulls.

yourdate > parmdate OR yourdate IS NULL

You might need to create the SQL dynamically to decide whether to include the or condition.

 

[RickSpr]

The abbreviated relation ">[Form]" tells me that you've given us the Criteria row for the Date column in your query grid. Try this:

Delete the Criteria from the Date column. In a new column, put the following expression on the Field row:
Nz([Date] > Forms![formname]![ctlname], True)
Uncheck the Show box for this column, and set the Criteria row to True.

What this does is compare [Date] to the date on the parameter form. If the form control isn't empty, this will return True or False. If the form control is empty, it will return Null, and the Nz function will convert that to True. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein