Determining the next Sunday from a date in a table.

[Bill Reed via AccessMonster.com]

Below is a table of dates used in the company to determine holidays and
workdays:

NGCID CalendarDay Weekday NonBusinessDay
2727 6/19/2005 1 Sunday
2728 6/20/2005 2
2729 6/21/2005 3
2730 6/22/2005 4
2731 6/23/2005 5
2732 6/24/2005 6
2733 6/25/2005 7 Saturday
2734 6/26/2005 1 Sunday
2735 6/27/2005 2
2736 6/28/2005 3
2737 6/29/2005 4
2738 6/30/2005 5
2739 7/1/2005 6 Holiday
2740 7/2/2005 7 Saturday
2741 7/3/2005 1 Sunday

I use this table in a query to determine the next Sunday following any given
date:

SELECT CalendarDay AS NextSunday
FROM tblCalendar
WHERE CalendarDay>=[Enter Date] And CalendarDay<DateAdd("d",8-Weekday([Enter
Date],2),[Enter Date]) AND Weekday=1

It works like a charm, except that I can't seem to use it anywhere to join on
a date field in another table and determine from that date what the next
Sunday date will be.

I can never seem to get my mind around dates! Please help.

Thanks

 

[Guest]

SELECT TOP 1 Table3.Calendar, Table3.Weekday, Table3.Name
FROM Table3
WHERE (((Table3.Calendar)>=[Enter date]) AND ((Table3.Weekday)=1));

 

[John Vinson]

I use this table in a query to determine the next Sunday following any given
date:

No table is necessary: you can use the builtin date functions.

NextSunday: DateAdd("d", 8 - Weekday([datefield]), [datefield])


John W. Vinson[MVP]

 

[Bill R via AccessMonster.com]

Thanks to all for your help.

John,

I need to take into account the company holidays. That would seem to
necessitate at least a table of holiday dates.
Dates always give me fits. Inasmuch as the table already existed, including
holidays, I thought I'd employ it.

Bill

I use this table in a query to determine the next Sunday following any given
date:

No table is necessary: you can use the builtin date functions.

NextSunday: DateAdd("d", 8 - Weekday([datefield]), [datefield])

John W. Vinson[MVP]