Doh!
Sorry, Jet doesn't understand Access constants. My bad.
You will need to use the literal value of vbMonday which is 2.
The for the Date, You will need to use Date().
StartDate: DateAdd("d", 2 - DatePart("w", Date()) -7, Date())
EndDate: DateAdd("d",Weekday(Date(), 1, [StartDate])
Another way to do this is to create hidden controls on your form that
have
the expressions as their control source. then your criteria for
StartTime
would be
BETWEEN "#" & Forms!MyFormName!StartDateControlName & "#" AND "#" &
Forms!MyFormName!EndDateControlName & "#"
:
I'm sorry, but I'm not doing something right. Okay, I have my query
open
in
design view. I go to next blank column and click Expression Builder
icon.
The box opens and I type in your function StartDate:
DateAdd("d",vbMonday - DatePart("w", Date) -7, Date) and then do
the
same
for EndDate. Next, I go to MY date field, which is titled StartTime
(date
and time field) and enter the criteria BETWEEN [StartDate] AND
[EndDate].
Doing it this way, I'm prompted for vbMonday and Date parameter
values.
When I enter thru those, I get "too complex" message.
If you would be so kind as to tell me what I'm doing wrong, I would
appreciate it.
Thanks,
Pam
The function I sent to find the Monday should replace your query
which
is
returning Sunday instead of Monday, however, neither of the
expressions
should be in the critera part of your query (assuming you are using
the
query
builder). The way to do this is to create two calculated fieds in
your
query
StartDate and EndDate
The Start Date field would have the name followed by " and then the
expression:
StartDate: DateAdd("d",vbMonday - DatePart("w", Date) -7, Date)
The End date woud be:
EndDate: DateAdd("d",Weekday(Date,vbMonday)-1, [StartDate])
The the Criteria field on your date field would be
BETWEEN [StartDate] AND [EndDate]
As to the error you got, be sure you copied the code correctly, I
tested
these expressions in the immediate pane, so there should not be a
syntax
problem.
:
Klatuu,
Thank you for answering. The expression I listed in my first
message
is
in
my query under StartTime criteria. Do I put the function you gave
in
the
same place? And do I combine them with "Between...And..."? If so,
I
tried
that and it returns an error message of "The expression you entered
has
an
invalid (dot) or ! operator or invalid parentheses" and the curser
flashes
on the parenthesis before the "w".
Thanks again,
Pam
This function:
DateAdd("d",-6-Weekday(Date()),Date())
Actually returns a Sunday date, not a Monday.
This function will return the Monday date for the previous week:
dtmFromDate = DateAdd("d",vbMonday - DatePart("w", Date) -7,
Date)
Then to get the To Date that will be the same day of the week one
week
prior
to today:
dmToDate = DateAdd("d",Weekday(Date,vbMonday)-1, dtmFromDate)
:
Hi,
I have a report that returns a list of work done weekly. The
expression
I
am using now is only good for looking at the report on Monday (7
days
prior). Work week is Monday thru Sunday. Is there a
calculation
that
will
give me the work completed and totaled from Monday to any day of
that
week.
Say if report was reviewed on Tuesday and then on Thursday, it
would
give
totals from Monday thru Tuesday and then again from Monday thru
Thursday.
Here is what I am using now:
Between DateAdd("d",-6-Weekday(Date()),Date()) And
DateAdd("d",1-Weekday(Date()),Date())
Any help is greatly appreciated!
Thanks,
Pam
