Date calculation problem

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

Hello All

I am using A97.

Is it possible to write a module function, or a query expression, that will
return the date of the latest Sunday (say) that is earlier than a certain,
specified date?
So e.g. given 14 April 2005, the function would return 10 April 2005.
I'm sure this should be possible, but I can't seem to get it myself and I
can't find it on any of the web resource sites that I know.

NB: I'm on UK format, so the dates in the above e.g. would 14/04/05 and
10/04/05.

Hope someone can help.

Many thanks
Les
 
I believe the weekday function works in Access 97, so this should do the
job:

([YOURDATE] - Weekday([YOURDATE], vbSunday)) + 1

Basically, the Weekday function returns an integer between 1 and 7,
relating to the day of the week. You can alter the start of the week using
the second argument, which in this case I have put as vbSunday.

What this means is that it will return the following for each day

Sunday = 1
Monday = 2
Wednesday = 3
Thursday = 4
Friday = 5
Saturday = 6
Sunday = 7

So you simply need to subtract the weekday number from the current date,
and then add one (this counters for the fact that if it is Sunday it will
return todays date).

Hope that helps

John Webb
 
Hello John

Many thanks for your code and explanation.
I wonder if you could also help with the next part of my problem: I need a
function that will add 26 records to table 'maternity', where the field
[weekending] is set to:

in the first record, the date of the first Sunday after the 'given' date
in the second record, the date of the second Sunday after the 'given' date
..
..
in the 26th record, the date of the 26th Sunday after the 'given' date

The other fields in 'maternity' are:
[staffname], and this needs to get the value of the text box called [sname]
on the current form (called 'frm main')
[monthname], and this needs to be set as the month name and the year name of
the [weekending] date (so for 15/4/05, [monthname] = April 2005)

I have in mind something like

For X = 1 To 26
INSERT INTO [maternity] ([weekending],[staffname],[monthname]) VALUES
DateAdd("w",X,([givendate] - Weekday([givendate], vbSunday)) +
1),[forms]![frm main]![staffname],Monthpart([weekending])&"
"&Yearpart([weekending])
Next X

Not surprisingly, my code is a bit off!
If you could set me on the right lines I would be extremely grateful.

Many thanks again
Les
 
Back
Top