Hi ,
I need to add automatically current weeks first date into a table and a text box of a vba form. could anyone help if any functions are available ??
Haven't tested it but I would imagine it would... it would just try to add 0 days (0 * -1)
Chris Klepeis
2009-07-21 16:27:41
When called on a Monday, it returns the previous Monday.
Patrick McDonald
2009-07-21 16:27:51
Weekday(Date(), 3) returns 7 for a Monday
Patrick McDonald
2009-07-21 16:28:28
My reference may be incorrect then. I was using this http://www.techonthenet.com/excel/formulas/weekday.php
Chris Klepeis
2009-07-21 16:46:06
Ah I see, I think that's the Excel function, not the VBA function, see http://www.techonthenet.com/access/functions/date/weekday.php
Patrick McDonald
2009-07-21 17:01:36
A:
I know you said VBA but here's how it can be done in Access database engine SQL and works 'stand alone' i.e. not requiring Access or VBA (of course, the logic can be translated into VBA):
Week beginning Sunday: if run on a Sunday will return the current date:
SELECT DATEADD('WW', DATEDIFF('WW', #1990-01-07 00:00:00#, NOW()), #1990-01-07 00:00:00#)
Week beginning Sunday: if run on a Sunday will return the previous Sunday:
SELECT DATEADD('D', (DATEDIFF('D', #1990-01-08 00:00:00#, NOW()) \ 7) * 7, #1990-01-07 00:00:00#)
Hint: 1990-01-07 00:00:00 is an arbitrary date known to be a Sunday.
onedaywhen
2009-07-22 10:02:48