Date Restrictor for this week

[ChuckW]

Hi,

Can someone tell me how to do a date restrictor which will alway capture
records for this week on a rolling basis? I have the following but it looks
like it is still capturing dates from last week:

WHERE (((FrontDesk.BusinessDate)>=DateAdd("d",-DatePart("w",Date()),Date())
And (FrontDesk.BusinessDate)<DateAdd("d",8-DatePart("w",Date()),Date())));

 

[Klatuu]

This will give you a Sunday through Saturday week for the current date:

WHERE FrontDesk.BusinessDate BETWEEN dateadd("d",vbsunday -
datepart("w",Date), Date) AND dateadd("d",6,dateadd("d",vbsunday -
datepart("w",Date), xdate));

Additional note - The BETWEEN executes faster than <= And >=

 

[ChuckW]

Hi,

Thanks for you help. I am still having some trouble with the date
restrictor you suggested. Here is my sql code:
-----------------------------------------------------------------------------------
SELECT FrontDeskLogCarola.BusinessDate, FrontDeskLogCarola.WeekOf,
FrontDeskLogCarola.Staff, NoRebookCarola.GuestName, NoRebookCarola.Reason
FROM NoRebookCarola INNER JOIN FrontDeskLogCarola ON NoRebookCarola.CPLogID
= FrontDeskLogCarola.CPLogID
WHERE FrontDeskLogCarola.BusinessDate between DateAdd("d",vbsunday -
datepart("w",Date), Date) and
dateadd("d",6,dateadd("d",vbsunday-datepart("w",date),xdate))
-------------------------------------------------------------------------------------------
I tried just plugging in your statement starting with the between into the
criteria section of the design layout. I tried both as is and adding a
parentheses at the end because you may have been short a closing parenthesis
but got the followign error message:

The expression you entered has an invalid (dot) or ! or operator or invalid
parentheses. When I click on OK, the cursor goes to your first
datepart("w".....

Any thoughts?

Thanks,

 

[Klatuu]

I'm not absolutely sure this is the problem, but try putting parenthesis
after the Date function.
WHERE FrontDeskLogCarola.BusinessDate between DateAdd("d",vbsunday -
datepart("w",Date()), Date()) and
dateadd("d",6,dateadd("d",vbsunday-datepart("w",Date()),Date()))

 

[ChuckW]

Hi,

Still getting the same error. My date restrictor does work with another
database I am using. The database that it work correctly with is only open
between Monday and Friday and closed on Saturday and Sunday. The database
that I am currently working with is for a company that is opened Monday
through Saturday and closed on Sunday. I think this is the root of my
problem. Here is the date restrictor:

=DateAdd("d",-DatePart("w",Date()),Date()) And

<DateAdd("d",8-DatePart("w",Date()),Date())

The date restrictor is supposed to capture everything for this week (this
monday through this friday) but is also capturing this past saturday which
should be from last week. Is there an adjustment I can make to this to
exclude last Saturday?

Thanks,

 

[Klatuu]

The code I posted selects Sunday through Saturday, so if they are closed, it
should not matter. I don't know why you are having a syntax problem with
what I sent. I don't have a database here I can test it on.

 

[John Spencer]

Your suggestion included a vb constant in the SQL. SQL does not recognize
vbConstants, you have to put in the actual value of the constant.

WHERE FrontDesk.BusinessDate BETWEEN dateadd("d",vbsunday -
datepart("w",Date), Date) AND dateadd("d",6,dateadd("d",vbsunday -
datepart("w",Date), xdate));

Should probably be more like
WHERE FrontDesk.BusinessDate BETWEEN dateadd("d",1- datepart("w",Date),
Date) AND
dateadd("d",7- datepart("w",Date), Date)

 

[Klatuu]

Doh!
I know better than that.
What was I thinking.
Thanks, John.