Help: Date Fields

[Dave]

Hi, I have a Sales database with two fields (actually more thaqn 2 but
these are causing me grief :), one is call [date received] and is
format as dd-mm-yyyy and the other is called day with the record
source as [date received] but the format as dddd.

What I would like to do is sort the [date received] dddd field to show
all sales on a certain day (i.e Friday. however when I right click and
filter by this field it picks up the ddd-mmm-yyyy field date and only
shows all sales on that date.

I am pretty familiar with access and can deal with a little bit of
coding but a basic solution would be great
]
Thanks

David

[Phil Stanton]

Re: Date Fields

Here is a SQL that accumulates dividends by day Is this what you want.
Table is called Dividends with the relevant fields as below

DividendID Autonumber
DivDate Date (dividend received)
UKInterest Currency


SELECT Left(Format([DivDate],"ddd-mmm-yyyy"),3) AS [Day],
Sum(Dividends.U KInterest) AS SumOfUKInterest ,
Left(Format([DivDate],"w-mmm-yyyy"),1) AS SortDay
FROM Dividends
GROUP BY Left(Format([DivDate],"ddd-mmm-yyyy"),3),
Left(Format([DivDate],"w-mmm-yyyy"),1)
ORDER BY Left(Format([DivDate],"w-mmm-yyyy"),1);

The ORDER BY bit just ensures it is printed out Sunday, Monday .....
Saturday

HTH

Phil


"Dave" <dave@fsnet.co. uk> wrote in message
news:288gj0lj04 pe0hafpjavqe2us 1qslc8lr0@4ax.c om...[color=blue]
> Hi, I have a Sales database with two fields (actually more thaqn 2 but
> these are causing me grief :), one is call [date received] and is
> format as dd-mm-yyyy and the other is called day with the record
> source as [date received] but the format as dddd.
>
> What I would like to do is sort the [date received] dddd field to show
> all sales on a certain day (i.e Friday. however when I right click and
> filter by this field it picks up the ddd-mmm-yyyy field date and only
> shows all sales on that date.
>
> I am pretty familiar with access and can deal with a little bit of
> coding but a basic solution would be great
> ]
> Thanks
>
> David[/color]