Dates: Using a query result in another query

[Bill R via AccessMonster.com]

I have a query:

SELECT tblCalendar.CalendarDay AS LastSunday
FROM tblCalendar
WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)
<DateAdd("d",8-Weekday((Now()-7),2),(Now()-7))) AND ((tblCalendar.Weekday)=1))
;

tblCalendar is a table of consecutive dates from 1998 thru 2020. It has
proven useful in many applications. CalendarDay is the date. The code above
returns the date of the Sunday preceding the day on which the query is run,
based on the system date. I had hoped to use this value in place of a prompt,
[Current WE Date], in an expression in a query field that looks like this:

IIf([Current WE Date]>=[Week Ending]![Week-end Date] And [Week Ending2]![Week-
end Date2] Is Null,"Not Yet Released RED","Planned Not Yet Released")

This query looks to another calendar table ([Week Ending]) which I would
dearly love to retire. This table has a field of consecutive dates and a
field of corresponding WE dates for each date. It is joined by the
consecutive date field in a query to a date field in a data table and the
corresponding WE date is determined from that.

Dates always confound me anyway, but can anyone see anyway I can use
LastSunday in place of the prompt? I already tried just replacing the prompt
with the LastSunday field, but I suspect there's a problem with the join.

There ought to be some way to replace a prompt for the date with the very
date the prompt is looking for as delivered by my LastSunday query, but I'll
be damned if I can figure it out!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Wayne Morgan]

You shouldn't need the table to get the date of the previous Sunday.

This will give you today's date if today is Sunday or the date of the
previous Sunday for any other day of the week using the current date from
the computer's clock.

Date() - Weekday(Date()) + 1

--
Wayne Morgan
MS Access MVP
"Bill R via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:51***********@AccessMonster.com...

I have a query:

SELECT tblCalendar.CalendarDay AS LastSunday
FROM tblCalendar
WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)
<DateAdd("d",8-Weekday((Now()-7),2),(Now()-7))) AND
((tblCalendar.Weekday)=1))
;

tblCalendar is a table of consecutive dates from 1998 thru 2020. It has
proven useful in many applications. CalendarDay is the date. The code
above
returns the date of the Sunday preceding the day on which the query is
run,
based on the system date. I had hoped to use this value in place of a
prompt,
[Current WE Date], in an expression in a query field that looks like this:

IIf([Current WE Date]>=[Week Ending]![Week-end Date] And [Week
Ending2]![Week-
end Date2] Is Null,"Not Yet Released RED","Planned Not Yet Released")

This query looks to another calendar table ([Week Ending]) which I would
dearly love to retire. This table has a field of consecutive dates and a
field of corresponding WE dates for each date. It is joined by the
consecutive date field in a query to a date field in a data table and the
corresponding WE date is determined from that.

Dates always confound me anyway, but can anyone see anyway I can use
LastSunday in place of the prompt? I already tried just replacing the
prompt
with the LastSunday field, but I suspect there's a problem with the join.

There ought to be some way to replace a prompt for the date with the very
date the prompt is looking for as delivered by my LastSunday query, but
I'll
be damned if I can figure it out!
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[Bill R via AccessMonster.com]

Wayne,

Oooh! That's nifty! That's why you get the big bucks!

Bill

Wayne Morgan wrote:

You shouldn't need the table to get the date of the previous Sunday.

This will give you today's date if today is Sunday or the date of the
previous Sunday for any other day of the week using the current date from
the computer's clock.

Date() - Weekday(Date()) + 1

I have a query:

[quoted text clipped - 33 lines]

I'll
be damned if I can figure it out!

--
Message posted via http://www.accessmonster.com

[BillCo]

you could refer to a function in your query which will return the next
friday from the date passed, or the current date if a friday is
entered.

function getWeekend(ByVal dt as Date)
Dim returnDt as date
returnDt = dt

Do While not weekday(returnDt) = 4
returnDt = returnDt + 1
loop

getWeekend = returnDt
end function

if you have a lot of records this may slow your query down - generally
it's not good to call functions from queries, but sometimes there is no
getting around it :s
Also this will run into problems where the weekend falls on thursday -
bank hols etc. But it's the best solution i can think of - but then
thats not saying much!

[BillCo]

just re-read your problem, you are looking for the previous sunday! ok,
use

while not weekday(returnDt) = 0
returnDT = returnDT - 1
loop

[Bill R via AccessMonster.com]

Thanks Bill,

I was just running into a problem trying to use the same functions to return
next Friday for some of the historic dates in the dataset. They used to use
the following Friday as the WE date, now they use the following Sunday, and I
can't come up with an integer to add to Date()-Weekday(Date()) to come up
with the next Friday (sometimes it requires a 13, sometimes a 5). So it looks
like I'll be using your function.

There'll be an extra 5$ in your pay envelope this week as well ;-)

Bill

BillCo wrote:

you could refer to a function in your query which will return the next
friday from the date passed, or the current date if a friday is
entered.

function getWeekend(ByVal dt as Date)
Dim returnDt as date
returnDt = dt

Do While not weekday(returnDt) = 4
returnDt = returnDt + 1
loop

getWeekend = returnDt
end function

if you have a lot of records this may slow your query down - generally
it's not good to call functions from queries, but sometimes there is no
getting around it :s
Also this will run into problems where the weekend falls on thursday -
bank hols etc. But it's the best solution i can think of - but then
thats not saying much!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200507/1

[jimfortune]

Wayne Morgan wrote:

You shouldn't need the table to get the date of the previous Sunday.

This will give you today's date if today is Sunday or the date of the
previous Sunday for any other day of the week using the current date from
the computer's clock.

Date() - Weekday(Date()) + 1

--
Wayne Morgan
MS Access MVP

Very Nice. I'm going to try:

Date() + (8 - Weekday(Date())) Mod 7

for today's date if today is Sunday or the next Sunday for any other
day of the week.

James A. Fortune