SELECTING RECORDS BASED ON 2 CONDITIONS 3

[thermalman]

Hi,I am using Crystal 10 with SQL backend.

I want to select all of the records from my Database between a StartDate and an EndDate based on when a calls TimeCompleted field being greater that 17:30.00.
The field is a DateTime field
but I also want to include ALL of the records that are completed during weekend dates.

Is this possible?

I have 2 parameters setup that ask me for a StartDate and an EndDate but whenever I try to create a formula to pull the information referencing the Calls completed after 17:30 I keep getting problems about it needing a date or that it is missing a ).

Can anybody please help?????

 

[lbass]

date({table.datetime}) in {?startdate} to {?enddate} and
(
time({table.datetime}) > time(17,30,0) or
dayofweek({table.datetime}) in [1,7]
)

-LB

 

[thermalman]

Thanks lbass,
I have entered the formula into my select expert and I am getting a lot of records.

I presume the first parts gives me the records based on my StartDate and EndDate. The secord part should include all the records where WorkOrders.TimeCompleted > 17:30 what does the third part do?

As I am getting records that are completed on a weekday before 17:30

Regards
Thermalman

 

[lbass]

Please copy your statement into the thread. Are you sure you added in the parentheses?

-LB

 

[thermalman]

Hi lbass,have tried it again and I am now getting calls that are over 17:30.

How can I ask the formula to also show me all of the calls that are performed during the day (not just after 17:30)on Weekends



date({WorkOrders.TimeCompleted})in{?StartDate}to{?EndDate} and
(time({WorkOrders.TimeCompleted})>time(17,30,0)or dayofweek({WorkOrders.TimeCompleted})in[1,7])

 

[Madawc]

Re-do the commands as two formula fields, boolians,

date({table.datetime}) in {?startdate} to {?enddate} and
(time({table.datetime}) > time(17,30,0)

dayofweek({table.datetime}) in [1,7]

Comment out the existing selection and put the two boolians with the details, where they will show 'true' or 'false'. This should tell you how the selection is failing and you can get it right. Then restore them as selections - you can just include the formula field name, if it is a boolian.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Your formula:

date({WorkOrders.TimeCompleted})in{?StartDate}to{?EndDate} and
(
time({WorkOrders.TimeCompleted})>time(17,30,0)or
dayofweek({WorkOrders.TimeCompleted})in[1,7]
)

...should already give you the correct results. Did you actually post your entire selection formula? I suspect you might have some other "or" clause that is causing the problem.

-LB

 

[Turkbear]

Hi,
In reading the original post, shouldn't the formula be:

(          // Main formula parens
 (         // first select condition in date range 
           //AND after 17:30
date({table.datetime}) in {?startdate} to {?enddate} and
time({table.datetime}) > time(17,30,0)
 )
 or
dayofweek({table.datetime}) in [1,7]  //OR on a weekend
)

Just a thought..



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

Turkbear,

I assumed the poster wanted the weekend dates to fall within the parameter period, also.

-LB

 

[Turkbear]

Hi,
It is a little ambiguous....The phrase

I also want to include ALL of the records that are completed during weekend dates

could apply to just the date range (eliminate the time criteria when a weekend) or could mean on a weekend regardless of date ...

I assume thermalman will know which...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[thermalman]

Hi,Many Thanks to lbass,madawc and Turkbear

I have retried lbass formula and it is now working okay.I was trying to get the formula to show me All calls that were completed between a StartDate and an EndDate that were completed after 17:30pm at night but I also wanted it to show me ALL records that were also completed at a Weekend as we Bill our Customers for Out Of Hours Calls during the week and the Weekend.

Hope this clears up any misunderstandings as to what I was trying to achieve.

Regards
Thermalman