Every Sunday Between Two Dates

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

I would like to run a querythat returns the date of every Sunday between two
dates. Is there an easy way to do this or do I need to list all of the dates
in a table?

Thanks in advance,
Chad
 
Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through whatever you maximum weeks times 7 will be.

Use this query --
SELECT DateAdd("d",[CountNUM],[Enter beginning date]) AS Sundays,
Weekday(DateAdd("d",[CountNUM],[Enter beginning date])) AS Expr2
FROM CountNumber
WHERE (((DateAdd("d",[CountNUM],[Enter beginning date])) Between
CVDate([Enter beginning date]) And CVDate([Enter ending date])) AND
((Weekday(DateAdd("d",[CountNUM],[Enter beginning date])))=1));


SELECT
 
Chad,

1. Start out creating a table (tbl_Numbers) with a single field
(lng_Number). Then add the values 0 through 9 to the table.

2. Next, create a query (qry_Numbers) from tbl_Numbers that generates a
series of number from 1 to 999 (if your dates are more than 999 days apart,
you will need to modify this query).

SELECT Hundreds.lng_Number * 100 + Tens.lng_Number * 10 + Ones.lng_Number as
lng_Number
FROM tbl_Numbers as Hundreds, tbl_Numbers as Tens, tbl_Numbers as Ones

3. Now you can create a query that will generate the dates you want (you
will also need to define the [Start Date] and [End Date] parameters.

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT DateAdd("d", lng_Number, [Start Date]) as Sundays
FROM qry_Numbers
WHERE DateAdd("d", lng_Number, [Start Date]) <= [End Date]
AND Weekday(DateAdd("d", lng_Number, [Start Date])) = 1
 
Back
Top