How to ask for "date is null or greater than 8/31/2011"

[bloomlight]

I am creating a query using Crystal report and need some help.

Basicly, the report is going to show "Active patient as of 8/31/2011".

* Patient discharge date is null
* OR discharge date is greater than 8/31/2011.

I know how to put IsNull(field), but don't know how to combine them together. Any ideas?

 

[nattyborneo]

Hi Bloomlight,

you can use :
isnull ({patient discharge date}) or {patient discharge date} >= 8/31/2011

rgds,
Nat

 

[bloomlight]

I tried that and got error "there is error in this formula....."

I do have other conditions in the query as well, such as "Patient Insurace is equal to Medicare". May be I have to do the following:

isnull ({patient discharge date}) and {patient insurace} = "Medicare"

Or {patient discharge date} >= 8/31/2011 and {patient insurace} = "Medicare"

Please help.

 

[nattyborneo]

Dear Bloomlight,

the posible problem is date format is incorrect that cause formula is error.

if it is long formula,you can add bracket

(isnull ({patient discharge date}) and {patient insurace} = "Medicare") Or ({patient discharge date} >= 8/31/2011 and {patient insurace} = "Medicare")

rgds,
Nat

 

[MCuthill]

Bloomlight,

Do you have any criteria other than what is shown above?

If not, this will cleanup the record selection:

(
IsNull({Table.Patient Discharge Date}) OR {table.patient discharge date} >= Date(2011,08,31)
) AND
{table.Patient Insurance} = "Medicare"

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bloomlight]

This worked.

 

[MCuthill]

Bloomlight,

Quick Question: Will this date (Aug 31, 2011) change to always be the last day of the prior month? If so, you can use the following to save updating the record selection each month.

To keep the record selection clean, I would create the following formula field to hold the "Trigger Date" for the report:
{@TriggerDate}:

[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),[blue]Month[/blue]([blue]CurrentDate[/blue]),1)-1

This formula determines the 1st of the current month and then goes to the day prior.

It can then be put in place of "Date(2011,8,31)" in my prior post for the Record Selection:

(
IsNull({Table.Patient Discharge Date}) OR {table.patient discharge date} >= {@TriggerDate}
) AND
{table.Patient Insurance} = "Medicare"

The {@TriggerDate} formula can also be put right into the selection criteria if you so choose. Simply put the contents of the formula above in place of "Date(2011,8,31)" in the record selection.

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bloomlight]

Thanks Mike. You are genius!!!!!

 

[bloomlight]

one more question: how to make the "TriggerDate" for (Aug 1, 2011) instead of (Aug 31, 2011)?

Thanks.

 

[MCuthill]

Bloomlight,

Assuming you would run this report in September 2011 for an August 1, 2011 "Trigger Date" - the following should work.

{@Trigger_StartofPriorMonth}

[blue]DateAdd[/blue]("m",-1,[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),[blue]Month[/blue]([blue]CurrentDate[/blue]),1))

Using the "DateAdd()" function, this formula will determine the 1st of the Current Month, then "Add" -1 Month from this date. Not to be confused with the "DateDiff()" function which is used to determine the number of a certain interval between two dates.

I used a formula with a few quick test dates for "CurrentDate" in it to check the above, and it works for all the test dates

Test Dates & Result of Formula:
CurrentDate -- August 1st, 2011
Aug 15/2011 -- July 1st, 2011
July 15/2011 -- June 1st, 2011
Jan 15/2011 -- December 1st, 2010
Sept 15/2010 -- August 1st, 2010

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

PS: I am hardly a genius... just lazy.
(or efficient, whichever)

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bloomlight]

Hi Mike, I need your help again. Today, I was asked to run the same report for last quarter, from 07/01/2010 to 09/30/2011. I tried to modify the query, but didn't work for me.

I have used the formula you gave to me:

DateAdd("m",-1,Date(Year(CurrentDate),Month(CurrentDate),1))

Do you have any idea how to edit this formula for report data from 07/01/2010 to 09/30/2011?

Thanks in advance.

 

[MCuthill]

Good Morning Bloomlight,

Would this be a one-off request, or will you be running a "quarterly" version at the end of every quarter?

I ask, because we can build a line that you just key the dates in for one-off date range requests, or I can develop a formula for you that will run the previous 3 months regardless of the month ran.

Please advise, I will have either solution posted shortly following your reply.

Thanks Bloomlight!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[bloomlight]

This query needs to be run on quarterly basis of any calendar year.

For example, this year:

01/01/2011 -- 03/31/2011
04/01/2011 -- 06/30/2011
07/01/2011 -- 09/30/2011
10/01/2011 -- 12/31/2011

Can you help me to set up a formula for each quarter?

Thanks a lot!

 

[MCuthill]

G'Mornin' Bloomlight,

No problem at all. I would suggest saving a new version of your Crystal Report for quarters (to save changing the criteria back and forth).

You can look at a Parameter that you can select either "Monthly" or "Quarterly" and have an IF in your record selection that toggles between the my prior solution and this one.

Either way; to return the previous quarter (assuming ran only in April, July, October & January), based on month it is ran in, please use the following formulae:
{@Trigger_StartofQuarter}

[green]//Add "-3" Months to the 1st of the Current Month.[/green]
[blue]DateAdd[/blue]("m",-3,[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),[blue]Month[/blue]([blue]CurrentDate[/blue]),1))

{@Trigger_EndofQuarter}

[green]//Find the start of the Current month, move back one day (-1)[/green]
[blue]Date[/blue]([blue]Year[/blue]([blue]CurrentDate[/blue]),[blue]Month[/blue]([blue]CurrentDate[/blue]),1)-1

Assuming the same selection criteria as I posted on Sept 19 at 13:46, the record selection would be as follows:
Record Selection:

(
[blue]IsNull[/blue]({Table.Patient Discharge Date}) [blue]OR[/blue] {table.patient discharge date} [blue]in[/blue] [{@Trigger_StartofQuarter} [blue]to[/blue] {@Trigger_EndofQuarter}]
) [blue]AND[/blue]
{table.Patient Insurance} = "Medicare"

I hope this helps Bloomlight, please advise should you have any questions or concerns.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."