Exclude certain week number records

[kernal]

Using Crystal 9 with a csv file.

Example of records in csv file (I don't know if this matters but for the report, I want a week to begin on Monday instead of Sunday):

Week Number for Start Date PROBLEM:

class day start date end date week_number
test 101-001 Mon 01/13/2010 04/28/2010 2
test 101-001 Mon 01/13/2010 04/28/2010 3
test 101-001 Mon 01/13/2010 04/28/2010 4
test 101-001 Mon 01/13/2010 04/28/2010 5
test 101-001 Mon 01/13/2010 04/28/2010 6 etc. until week 17 since it has an ending date of 04/28/2010

I need to get rid of week 2 because the class is held on Mon and the START date in the csv file is Wed, 01/13/2010 but the class really STARTS in week 3 on 01/18/2010.

Week Number for End Date PROBLEM:


test_201-020 Sat 01/11/2010 04/28/2010 2
test_201-020 Sat 01/11/2010 04/28/2010 3
test_201-020 Sat 01/11/2010 04/28/2010 4
test_201-020 Sat 01/11/2010 04/28/2010 5
test_201-020 Sat 01/11/2010 04/28/2010 6 etc. until week 17 since it has an ending date of 04/28/2010

I need to get rid of week 17 because the class is held on Sat and the END date in the csv file is Wed, 04/28/2010 but the class really ENDS in week 16 on 04/24/2010.

Help is very appreciated.

 

[lbass]

How are you currently selecting records? What is the formula for weeknumber?

-LB

 

[Madawc]

DatePart will give you a week number, among other things. It also lets you choose when a week starts, format DatePart (intervalType, inputDateTime, firstDayOfWeek)

I'd suppose you'd want something like

DatePart("ww", {end.date}, crMonday)

If that's not it, please explain more. If the {end.date} is not reliable, you could use a formula field to get the correct date and then apply DatePart to the result.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[kernal]

The week_number is in the csv file. The csv file is created from a select statement in a query manager. It looks at just what week numbers are the start date and end date in and doesn't take into account what day of the week is the class.

Thanks

 

[lbass]

But what is the start of the week in the CSV file? And what it the startdate for the year in the CSV file? You will have to use that information to address the current issue, so please report back with the query or the information on how the first day of the year is set and the first day of the week. For example, in CR, you have the option of setting the first day of the year based on FirstJan1, FirstFourDays, FirstFullWeek, or the system default.

-LB