Null Criteria

[Tom Knight]

Hi, I'm using a form to generate criteria for a query column that contains a date, the problem is that i only want a criteria generated if a field contains a value. If the field is empty I want the query to show all records
I've entered the following...

CRITERIA: IIf(IsNull([Forms]![Find_Record_Form]![Purchased_From2]), ,Between [Forms]![Find_Record_Form]![Purchased_From2] And [Forms]![Find_Record_Form]![Purchased_To2])

i'm looking for something to fill the gap which will do the job. If it was a text field i could use Like and put "*" in the gap but this doesn't work with dates.
Can anyone help?
Thanks, Tom.

 

[wcrc]

You could try to use the Query builder and use the IS null property on the second line .

In essence saying betweeen date1 and date2 OR Is Null

Try this:
Criteria
Between [Forms]![Find_Record_Form]![Purchased_From2] And [Forms]![Find_Record_Form]![Purchased_To2]

Second line
Is Null

 

[Mile-O]

Try this:


Create a calculated field in your query:

NewField: Between [Forms]![Find_Record_Form]![Purchased_From2] And [Forms]![Find_Record_Form]![Purchased_To2])

Put the criteria: Is Not Null

 

[Tom Knight]

Thanks mate, that works. Appreciate the help. I can't believe i've been trying combinations for hours and it's really that simple... Oh well always the way i suppose!