By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,988 Members | 1,049 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,988 IT Pros & Developers. It's quick & easy.

Help: Date Fields

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.UKInterest) 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" <da**@fsnet.co.uk> wrote in message
news:28********************************@4ax.com...
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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.