Can't 'Group On Week' for this Particular Date Field 1

[Mike555]

Hi. I have a report which has "WeekOf" as a Sorting/Group Expression. The report is bound to a query and the "WeekOf" field is formatted as follows within that query...

Format(IIF(Format(DateAdd("d",-Weekday([ECD_CutOff]),[ECD_CutOff])+1,
 "Short Date")<IIF(Weekday(Date())=1,Date()+6,IIF(Weekday(Date())=2,Date()+5,
IIF(Weekday(Date())=3,Date()+4,IIF(Weekday(Date())=4,Date()+3,
IIF(Weekday(Date())=5,Date()+2,IIF(Weekday(Date())=6,Date()+1,
IIF(Weekday(Date())=7,Date(),))))))),IIF(Weekday(Date())=1,Date(),
IIF(Weekday(Date())=2,Date()+-1,IIF(Weekday(Date())=3,Date()+-2,
IIF(Weekday(Date())=4,Date()-3,IIF(Weekday(Date())=5,Date()-4,
IIF(Weekday(Date())=6,Date()-5,IIF(Weekday(Date())=7,Date()-6,
))))))),Format(DateAdd("d",-Weekday([ECD_CutOff]),[ECD_CutOff])+1,
 "Short Date")),"Short Date") AS WeekOf

The problem is that within the report's Sorting/Grouping I cannot specify that the WeekOf field should GROUP ON Week. The only Group On options for this field are "Each Value" & "Prefix Characters", as if this is a non-date field. What must I do to either the report or the query to make this report recognize this field as a Date field so that I can Group On Week? Thanks.


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[RoyVidar]

I think you hit the nail there "as if this is a non-date field." - cause, after you've utilized the format function on the date, the return is a string...

So if you need one field formatted a particular way, do the formatting on that field in the query, but allow for another field, using the same criteria, which is a date field throughout, to be grouped on (remove all the format thingies).

One question though, woudn't it be esier to propagate a date through the query, then do the formatting in the text control of the report?

Roy-Vidar

 

[Mike555]

So if you need one field formatted a particular way, do the formatting on that field in the query, but allow for another field, using the same criteria, which is a date field throughout, to be grouped on (remove all the format thingies).

RoyVidar, I'm not sure I understand what this statement means. Specifically "a date field throughout". Could you elaborate further on this?

Thanks for your help - it is greatly appreciated


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.

 

[RoyVidar]

Just what I said, your current field, because you've utilized the format function on it, is a string, not a date. If you need that string/text representation of a date, just keep it.

But for the date grouping, you will need a date field, so try using the same criteria on a new field (iifs 'nall), without converting it to text -> "a date field throughout" - on which you will be able to do group by in the reports sorting/grouping thingie.

Roy-Vidar

 

[Mike555]

Understood - Thanks it works now!


--
Not until I became a Network Administrator did the error message "See your Network Administrator for assistance" become petrifying.