Prev: FROM and JOIN Problems
Next: IIF function help in query
From: rbeach on 13 May 2010 08:57 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
From: rbeach on 13 May 2010 09:10 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
From: rbeach on 13 May 2010 14:13 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
From: Daryl S on 17 May 2010 14:25 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
|
Pages: 1 Prev: FROM and JOIN Problems Next: IIF function help in query |