Group report by specific date & time range w/o parameters

[former_member1115112]

I am trying to create a report to show the total product weight that our company brings in per fiscal week, but our week days & hours are set differently due to inventory than a regular calendar. I am trying to group the weight per week, but I am having a hard time coming up with a formula to do this for me rather than a parameter for each day. Our weeks are Monday 6:00:00am to the following Monday 5:59:59am.

[DellSC]

This should get you the week number based on your date. Then you can group on the week number.

If DayOfWeek({MyTable.DateTimeField}, crMonday) >= 1 or DatePart(h, {MyTable.DateTimeField}) >= 6 then
  ToText(Year({MyTable.DateTimeField}, 0, '') + Right('0' + ToText(DatePart(ww, {MyTable.DateTimeField}, crMonday, crFirstJan1), 0, ''), 3)
else  //it's a Monday before 6 am
  ToText(Year({MyTable.DateTimeField}, 0, '') + Right('0' + ToText(DatePart(ww, {MyTable.DateTimeField} - 1, crMonday, crFirstJan1), 0, ''), 2)

If you need to set the group label to show dates, try something like this:

LocalDateVar weekStart;
LocalDateVar weekEnd;
weekStart := {MyTable.DateTimeField} - DayOfWeek({MyTable.DateTimeField}, crMonday) + 1;
weekEnd := weekStart + 6;
ToText(weekStart, 'MM/dd/yyyy') + ' to ' + ToText(weekEnd, 'MM/dd/yyyy')

Don't use this second formula to group on because it won't sort correctly due to converting the dates to string. Instead, group on the year and week string from the first formula and just use this formula as the group label.

-Dell

[former_member1115112]

Hello Dell, thanks for you help. I am trying to put it in a cross tab and group on the date to display the fiscal week. I tried the first formula you recommended but I am getting an error. Where the "or" is it is saying "a number, currency amount, boolean, date or string is expected here" I cannot seem to figure out whats wrong with the formula. Any suggestions?

[DellSC]

Sorry about that - it should be ">= 1 or"

-Dell