Select Friday and Saturday dates between two fields 1

[Cozmo2]

I am trying to retrieve all the Friday and Saturday dates between two datetime fields (2/18/2011 - 3/19/2011). The output needs to look like: February 18 - 19 2011; March 4 - 5 2011; etc. I am using Crystal 9. Any help/advise would be greatly appreciated.

 

[kray4660]

Using DayOfWeek function in the selection criteria along with the date criteria. Something like this.

{Date} in DateTime (2011, 02, 18, 00, 00, 00) to DateTime (2010, 03, 19, 00, 00, 00) and
(DayOfWeek({Date}) = 7 or DayOfWeek({Date}) = 1)

There is also a variation of DayOfWeek where you can set the first day of the week.

I hope this helps.

 

[lbass]

1) Are these two different fields or two instances of the same field or two different parameters?

2) Is there a date in the database for every day between the two dates, or could the dates you want have no records in the database?

3) In what report section do you intend to display the result?

-LB

 

[Cozmo2]

There are two different fields involved:
A course is scheduled to begin on Start-Date and end on End-Date and only has classes on Friday & Saturday. I need to give the all the Friday and Saturday dates between the Start-Date and the End-Date.

There is not date in the database for every day between the two dates. The dates are from a record on the database.

This should be in the Detail section.
Thanks for your help.

 

[lbass]

If there are ONLY records for Friday and Saturday and you want to see them in the detail section, then you just need to use a formula like this:

if month({table.startdate}) = month({table.enddate}) then
totext({table.startdate},"MMMM d")+" - " +
totext({table.startdate},"d yyyy") else
totext({table.startdate},"MMMM d")+" - " +
totext({table.startdate},"MMMM d yyyy")

-LB

 

[Cozmo2]

What I am trying to accomplish is:
The database contains the following records:
Course_ID Start_Date End_Date
AAAA 2/18/2011 3/15/2011
BBBB 2/18/2011 3/15/2011
CCCC 3/25/2011 5/23/2011

I want to list the Friday & Saturday dates between the start date and the end date for each course. I'm sorry I'm not making myself very clear.

 

[lbass]

Place this formula in the detail section:

numbervar i := i + 1;
numbervar j := datediff("d",{table.startdate},{table.enddate});
stringvar x := "";
for i := 1 to j do(
if not(totext({table.startdate}+i,"MMMM d") in x) then(
if dayofweek({table.startdate}+i)=7 then (
if month({table.startdate}+i) = month({table.startdate}+i+1) then
x := x + totext({table.startdate}+i,"MMMM d") + " - " +
totext({table.startdate}+i+1,"d yyyy")+ "; " else
x := x + totext({table.startdate}+i,"MMMM d") + " - " +
totext({table.startdate}+i+1,"MMMM d yyyy")+"; "
)));
i := 0;
x

-LB

 

[Cozmo2]

Thanks lbass. I learn so much from your help. Thanks again!