Displaying Null values and a Date range

[Coldsteel]

Hello all,

I am trying to make a query which displays all my records which are with in a date range and also the ones which do not a value yet.
Example:
Search: My date range is between 3/01/2009 to 3/06/2009

Records Found: Blank, 3/01/2009, 3/09/2009, Blank

Here is my Sql Where Statment:

WHERE (((tbl_referral.[Time Closed])=IIf([Time Closed] Between DateSerial(Year(Date()),Month(Date()),1) And Now() Or ([tbl_referral].[Time Closed])=IsNull([Time Closed]),[Time Closed],"")));

But Keep getting data only within the data range anyone know what I am doing wrong

Thanks,
Mike

 

[pbaldy]

Try

WHERE [Time Closed] Between DateSerial(Year(Date()),Month(Date()),1) And Now() OR [Time Closed] Is Null

 

[Coldsteel]

You are a Genius!!!

Thanks for the Help

 

[pbaldy]

No problem Mike. You had the right idea, but the wrong syntax.

 

[Coldsteel]

Hi Guys,

I think I have a problem, I didnt notice this before but for some reason my SQL Statment does not pull all the dates I am looking for.
Example: 3/1/2009 3/10/2009 3/11/2009 pull normally, but 3/9/2009 does not pull on my search.

Here is the updated SQL Statment:WHERE [Time Closed] Between DateSerial(Year(Date()),Month(Date()),1) And Now() OR [Time Closed] Is Null

Does anyone know if I am missing something?

Thanks,
Mike