How to make report records with correct date?

[bloomlight]

I have a query in Crystal report with the following formula:

{StartOfCareDate} <= DateTime (2012, 01, 31, 0, 0, 0) and
(IsNull ({Discharge}) OR {Discharge} >= Date(2012,01,01))

Basically, I need a report which will show all active patients in the month of Jan. 2012. But with the formula, the report shows a lot of patients "Startofcaredate prior to 01/31/2012 and dischared before 01/01/2012".

The report is showing this:

startDate DischargeDate
---------- -------------
05/12/2003 01/13/2012
05/20/2003 01/13/2005 (don't need)
02/01/2008 10/20/1010 (don't need)
08/01/2010 null
01/07/2011 11/25/2011 (don't need)
03/10/2011 01/25/2012
01/17/2012 01/29/2012
01/22/2012 null


What I really need is this:

startDate DischargeDate
---------- -------------
05/12/2003 01/13/2012
08/01/2010 null
01/03/2011 01/25/2012
01/17/2012 01/29/2012
01/22/2012 null

I don't know why my formula didn't work.
Any help will be greatly appreciated.

 

[lbass]

I don't see how you could be getting those results unless you are not showing the entire formula (and that is my guess).

Note that if you want to include 1/31, you should be using:

{StartOfCareDate} <= DateTime (2012, 01, 31, 23, 59, 59)

Or more simply:

{StartOfCareDate} < DateTime (2012, 2, 1, 0, 0, 0)

Please show your entire formula.

-LB

 

[bloomlight]

Thanks for getting back to me so soon. I got it right.

What happen was I put the formula under the "formula fields" with other conditions under "formula editor" area. As soon as you mentioned - this is not the entire formula, I moved the formula to the same place where other conditions are. The report shows the correct records now.

Thanks again.