Selecting records by future schedule date

[RepRider]

CRXI and SQL Server

I have a report that I want to select records by the appointment dates in our scheduler tables. I want to automate it to run on a wednesday but choose all the clients who are scheduled for Dr. appts for the next week (M-F).

It is run now by a person manually selecting the appt dates for the next week. How do I rewrite the selection formula to automatically select for the next weeks dates based on the report run date? I know how to make it automatically run every wednesday in Infoview.

The current selection formula is:

not ({Program.program_name} in ["DD ALZHEIMERS RESPITE", "DD COMMUNITY INPATIENT", "DD EMERGENCY SERVICES", "DD FAMILY SUPPORT", "DD STATE INPATIENT", "MI ADULT COMMUNITY INPATIENT", "MI ADULT CRISIS RESIDENTIAL", "MI ADULT EMERGENCY SERVICES", "MI ADULT GERIATRIC ASSESSMENT", "MI ALZHEIMERS RESPITE", "MI CHILD COMMUNITY INPATIENT", "MI CHILD CRISIS RESIDENTIAL", "MI CHILD EMERGENCY SERVICES", "MI CHILD STATE INPATIENT"]) and
{Service_Group.service_group_name} in ["ACT", "ACT TEAM", "HOME BASED", "HOME BASED CLUSTER", "PSYCHIATRIC", "PSYCHIATRIC CLUSTER"] and
{Authorizations.archived} = 0 and
{Authorized_Service_View.archived} = 0 and
{Authorizations.authorization_end_date} >= DataDate - 45 and
not ({Sch_Entry.name} like ["*CANCEL*", "*RESCH*"]) and
{Sch_Entry.start_date} in DateTime (2009, 06, 22, 0, 0, 0) to DateTime (2009, 06, 26, 0, 0, 0)

It seems like I should put a DayofWeek formula in and use that but I am not sure. Can anyone assist me? Thanks!

 

[republiccommando]

If the job is scheduled to run every Wednesday, and you want to capture Mon - Fri appts. next week, you could:

create 2 formulas:

MonDate = today()+6
FriDate = today()+10

then your selection at the end would be

...
{Sch_Entry.start_date} in {@MonDate} to {@FriDate}

Hope this helps...

 

[republiccommando]

ooops...it is 5 and 9 instead of 6 and 10

 

[RepRider]

Is that an open parens after the word today?

 

[RepRider]

ok, I got it plugged in but it did not return data for the Thursday of that week. Actually, there were no appts scheduled for Wed or Fri. Do I need to account for that?

There is a preset formula for LastFullWeek. Is there a way to create a formula for NextFullWeek? That would be the best solution for what I need. I don't know how to do that.

 

[RepRider]

Nevermind... I found it in another search!!!

thread767-1291407

Works perfect!!! You guys/gals are FABULOUS!!!