Conditional WHERE clause on form query

[vicissitude]

I wonder if someone could help me with this one,

I have a from which has StartDate and EndDate fields. It also has a WeekBeginning unbound textbox. I want the form to filter on these dates so the form only displays the record if it is...

a. after StartDate and before EndDate
b. after StartDate (if no EndDate exists)

The problem is b where no EndDate exists, how do either have a WHERE Clause if an EndDate exists or no WHERE clause if no EndDate exists?

Thanks.

 

[CBrighton]

What is it filtering?

Its own recordsource? A sub form? A query? A report?

 

[vbaInet]

The criteria under EndDate will be:

[Forms]![[COLOR=Red]Form Name[/COLOR]]![[COLOR=Red]EndDate Control Name[/COLOR]] Or [Forms]![[COLOR=Red]Form Name[/COLOR]]![[COLOR=Red]EndDate Control Name[/COLOR]] Is Null

Edit the bits in red.

 

[vicissitude]

the form has a 'week beginning date' and the records in the subform need to be filtered by that date, i.e. only records that should appear are ones that have a StartDate after the 'week beginning date' AND have a EndDate before ('week beginning date' +6) OR no EndDate at all.

on the query criteria at the moment i have.....

on StartDate:
<=[forms]![Sessions].[txtWkBeginDate]+6
(less than or equal to Sunday of that week)

on EndDate
>=[forms]![Sessions].[txtWkBeginDate] Or IsNull([EndDate])
(more than or equal to Monday of that week)

The form does not display any record that does not have an EndDate.

 

[vbaInet]

In my reply I didn't write the IsNull() function. I wrote Is Null. Follow exactly what you were given.

 

[vicissitude]

Yea got it....

EndDate
>=[forms]![Sessions].[txtWkBeginDate] Or [EndDate] Is Null

Thats great, Thanks!

For future reference what makes the difference between the IsNull and Is Null?

Thanks

 

[vicissitude]

O right IsNull is a Function......

didn't know that,

Thanks

 

[vbaInet]

They are pretty much the same in their functionality but different in their use. IsNull() is a VBA function that returns true or false, whereas Is Null is used mainly in queries to filter Null values within a recordset. So to conclude, only use Is Null in a query criteria but you can use IsNull() within columns in a query.

If you wrote this:
>=[forms]![Sessions].[txtWkBeginDate] Or IsNull([EndDate])=True

... it will work. But that will compute slower than Is Null.