VBA Date Function

[Guest]

I have in a sales tbl of 19,000 records and I'm trying to acheive 2 date
measurements, first of all Weeks, because the financial year starts in April
I have created an unbound field that automatically calculates using
=DateDiff("ww",[FinYear],[Date])+1 which works perfectly, but my problem
starts when I try to compare The Yearly Moving Annual Total, Which compares
the same day Previous Year and Current year. i.e. 22 Week2003 v's 22 Week
2004 then Monday v's Monday. Now its obvious that I can't use date ranges so
I thought the best solution would be to enter a number that I can then attach
an inner join to.

Soo.....

Monday to Sunday would be = 1 -7, so I could set parameters to 22 and 1

so for example, If I typed in a form field 29/08/04, I would want a week
field and a day field to calculate...... Week = 22 Day = 7 / Sunday

I have tried multiple versions of the VB Code supplied in multiple access
bibles I have, but none seem to be able to continuously return a value from 1
- 7.

I hope anyone out their can understand what I'm trying to acheive here!
Any help would be appreciated.

Cheers
dan

 

[[MVP] S.Clark]

DateSerial is a function that allows the construction of a date with the
supplied parameters of year, month, day. I think it may help in this
situation.