look at a date and return the following sunday

[Guest]

I need to show failures to meet target by week, with the last day of the week
being Sunday. So if a failure occurred on 07/08/06 I need to list it against
13/08/06.

Is there a neater way than If day = Monday add 6 days to date, if date =
Tuesday add 5 days to date?

Thanks

 

[Guest]

This gives you the numerical week of the year with Monday as the first day of
the week (Sunday as last day).

Format([YourDateField],"ww",2)

 

[RoyVidar]

I need to show failures to meet target by week, with the last day of
the week being Sunday. So if a failure occurred on 07/08/06 I need
to list it against 13/08/06.

Is there a neater way than If day = Monday add 6 days to date, if
date = Tuesday add 5 days to date?

Thanks

Perhaps something like this

NewDate = MyDate - Weekday(MyDate, vbMonday) + 7

If you're thinking queries, the number represented by the vbMonday
constant, is 2

 

[Guest]

How about:

=DateAdd("d",8-Weekday(MyDate),MyDate)

Allan

 

[Guest]

Thanks, that works , though I had to use 7-Weekday not 8-Weekday

Steph