A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Query from day of week



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 01:57 PM posted to microsoft.public.access.queries
rbeach
external usenet poster
 
Posts: 48
Default Query from day of week

I need assistance in creating the formula to select one week ago Wednesday
through Thursday of the current week. This query needs the ability to be run
on any day of the week. Below is the formula I have in place but it returns 8
days ago through yesterday. If this is run on Wednesday it works correctly
but I need it to be run on any weekday.

=Date()-Weekday(Date())-2+1*(Weekday(Date())=4) And =Date()-Weekday(Date())+3+1*(Weekday(Date())=1)


--
Rick
  #2  
Old May 13th, 2010, 02:10 PM posted to microsoft.public.access.queries
rbeach
external usenet poster
 
Posts: 48
Default Query from day of week

My apologies but I stated "one week ago Wednesday through Thursday" below but
it should read "one week ago Wednesday through Tuesday".
--
Rick


"rbeach" wrote:

I need assistance in creating the formula to select one week ago Wednesday
through Thursday of the current week. This query needs the ability to be run
on any day of the week. Below is the formula I have in place but it returns 8
days ago through yesterday. If this is run on Wednesday it works correctly
but I need it to be run on any weekday.

=Date()-Weekday(Date())-2+1*(Weekday(Date())=4) And =Date()-Weekday(Date())+3+1*(Weekday(Date())=1)


--
Rick

  #3  
Old May 13th, 2010, 07:13 PM posted to microsoft.public.access.queries
rbeach
external usenet poster
 
Posts: 48
Default Query from day of week

I had the below with "=4" and it should have been "=4".

--
Rick


"rbeach" wrote:

My apologies but I stated "one week ago Wednesday through Thursday" below but
it should read "one week ago Wednesday through Tuesday".
--
Rick


"rbeach" wrote:

I need assistance in creating the formula to select one week ago Wednesday
through Thursday of the current week. This query needs the ability to be run
on any day of the week. Below is the formula I have in place but it returns 8
days ago through yesterday. If this is run on Wednesday it works correctly
but I need it to be run on any weekday.

=Date()-Weekday(Date())-2+1*(Weekday(Date())=4) And =Date()-Weekday(Date())+3+1*(Weekday(Date())=1)


--
Rick

  #4  
Old May 17th, 2010, 07:25 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Query from day of week

Rick -

If today is Monday, do you want the period to be the Wednesday from two
weeks ago through the Tuesday from last week? Or do you want to end always
on the current week, even if the Tuesday has not passed yet?

If you always want to end on this week's Tuesday, then the logic would be to
subtract the number of days passed in this week (Weekday()) and add 3 for
Tuesday. And to start on the prior Wednesday, then subtract the number of
days passed in this week plus one week (Weekday() + 7) and add 4 for
Wednesday. The result would look like this:


= (Date()-Weekday(Date())-7+3) And = (Date()-Weekday(Date())+2)

Or simpler:

= (Date()-Weekday(Date())-4) And = (Date()-Weekday(Date())+2)


If you want to go to the prior week's data if today is Sunday or Monday,
then use this:

= (IIf(Weekday(Date()) 2,Date()-Weekday(Date())-11,Date()-Weekday(Date())-4) And = (IIf(Weekday(Date()) 2, Date()-Weekday(Date())-5,Date()-Weekday(Date())+2))


--
Daryl S


"rbeach" wrote:

I had the below with "=4" and it should have been "=4".

--
Rick


"rbeach" wrote:

My apologies but I stated "one week ago Wednesday through Thursday" below but
it should read "one week ago Wednesday through Tuesday".
--
Rick


"rbeach" wrote:

I need assistance in creating the formula to select one week ago Wednesday
through Thursday of the current week. This query needs the ability to be run
on any day of the week. Below is the formula I have in place but it returns 8
days ago through yesterday. If this is run on Wednesday it works correctly
but I need it to be run on any weekday.

=Date()-Weekday(Date())-2+1*(Weekday(Date())=4) And =Date()-Weekday(Date())+3+1*(Weekday(Date())=1)

--
Rick

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:04 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.