Query to create dates

[BigPondNewGroups]

I want to create a query that returns the dates of the next five Fridays.
Is this possible?
Simon

 

[Allen Browne]

Next Friday is:
Date() - Weekday(Date()) + 6

If you want a query that gives you 5 records for the next 5 Fridays:
1. Create a table with 1 field of type Number, named (say) CountID.
Mark the field as primary key.
Save the table as (say) tblCount.
Enter the records 0 to 4 (5 records all up.)

2. Create a query based on this table.
In the Field row, enter:
TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID]

 

[Guest]

Simon,

I have a table I call tbl_Numbers, which contains a single long integer
field lngNumber, which has 10 records, 0 - 10. I then have a query
(qry_Numbers) that generates numbers from 0 to 999, as follows:

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

I then use this query for a variety of queries. In your case, I would write
a query that looks like:

SELECT TOP 5 DateAdd("d",[lngNumber],Date()) AS Expr1
FROM qry_Numbers
WHERE Weekday(DateAdd("d",[lngNumber],Date()))=6;

HTH
Dale

 

[Simon Tafe]

Thank youi very much
That worked brilliantly
Simon

Next Friday is:
Date() - Weekday(Date()) + 6

If you want a query that gives you 5 records for the next 5 Fridays:
1. Create a table with 1 field of type Number, named (say) CountID.
Mark the field as primary key.
Save the table as (say) tblCount.
Enter the records 0 to 4 (5 records all up.)

2. Create a query based on this table.
In the Field row, enter:
TheDate: Date() - Weekday(Date()) + 6 + 7 * [CountID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I want to create a query that returns the dates of the next five Fridays.
Is this possible?
Simon