[AccessD] A2K:CrossTab Question

Perfect!!!!
Access 2K - No WeekDayName Function - you are correct
Many thanks Gustav
cha cha cha

Darren


----- Original Message ----- 
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Tuesday, August 03, 2004 9:53 PM
Subject: Re: [AccessD] A2K:CrossTab Question


> Hi Darren
>
> > I have a table that records the dates of occurences - so far so good. The table name is tblOffences, the Field is called
OffenceDate
> > (very original - I know:-)) Date /Time data format Date Format = dd/mm/yyyy
> >
> > Then I need a sum of 'em. EG over the whole year 100 occurences on Mondays, 200 on Tuesdays
> > 120 on Wednesdays etc.
>
> > Of course I have dates that span the whole year, I just want to know what the syntax or procedure
> > is for putting a years worth of data into 1 of 7 groups (ie each dayo the week)
>
> Stuart's WeekdayName() is a newer function from A2000 or A2002.
>
> So, for you with A97, that would be:
>
>   Select
>     Format(OffenceDate, "dddd", 2) As DayOfWeek,
>     Weekday(OffenceDate, 2) As DayNumber,
>     Count(OffenceDate) As OffenceCount
>   From
>     tblOffences
>   Where
>     Year(OffenceDate)=2004
>   Group By
>     Format(OffenceDate, "dddd", 2),
>     WeekDay(OffenceDate, 2)
>   Order By
>     WeekDay(OffenceDate, 2);
>
> /gustav
>
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com