DateAdd and DatePart

[margaret hannah]

I've included the thread below of the actual problem and
resolution. However, while I understand what the problem
is, I don't know what the solution is.

My problem: my work week is Monday thru Sunday. If I use
the following criteria in my query and I put in Sunday's
date, I can't pick up Sunday. For instance, I put in 9/5
or 9/4 or even 8/30 (which is Monday), it won't pick up
9/5 (the Sunday of that week). I understand it's because
Sunday is "1" and I'm asking to start on "2". But how can
I get it to end on "1" ...

Between DateAdd("d",2-DatePart("w",[Beginning Date:]),
[Beginning Date:]) And DateAdd("d",7-DatePart("w",
[Beginning Date:]),[Beginning Date:])

That worked like a champ ... thanks ever so much.

-----Original Message-----

I have a qyr (QRYEmployee Time) that asks for a beginning
date and ending date. Is there anyway that I can have an
error message if the dates are not equal to 7 days. For
example, my beginning date is 8/30/2004 and my ending date
is 9/5/2004. But if I type 9/4/2004 or 9/6/2004, I will
get an error message?

Thanks for any help.

No user error message capability in Queries, but....

If you always want to have exactly 7 days, use:
Between [Enter Date] and DateAdd("d",7,[Enter Date])

Or use a form for parameter entry.
In which case the query criteria would be:

Between forms!FormName!StartDate and forms!FormName! EndDate

In the form you can code a command button click event:

If [EndDate] < [StartDate]+ 7 then
MsgBox "You must enter an End date 7 days later than the start date."
[EndDate].SetFocus
End If
' Then continue with whatever other code you need.

You can enter an EndDate greater than 7 days, but not less.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

..

 

[Michel Walsh]

Hi,


DatePart has a third argument specifying which day is the first day of the
week (in your example, use vbMonday, or 2).


Hoping it may help,
Vanderghast, Access MVP

I've included the thread below of the actual problem and
resolution. However, while I understand what the problem
is, I don't know what the solution is.

My problem: my work week is Monday thru Sunday. If I use
the following criteria in my query and I put in Sunday's
date, I can't pick up Sunday. For instance, I put in 9/5
or 9/4 or even 8/30 (which is Monday), it won't pick up
9/5 (the Sunday of that week). I understand it's because
Sunday is "1" and I'm asking to start on "2". But how can
I get it to end on "1" ...

Between DateAdd("d",2-DatePart("w",[Beginning Date:]),
[Beginning Date:]) And DateAdd("d",7-DatePart("w",
[Beginning Date:]),[Beginning Date:])

That worked like a champ ... thanks ever so much.

-----Original Message-----

I have a qyr (QRYEmployee Time) that asks for a beginning
date and ending date. Is there anyway that I can have an
error message if the dates are not equal to 7 days. For
example, my beginning date is 8/30/2004 and my ending date
is 9/5/2004. But if I type 9/4/2004 or 9/6/2004, I will
get an error message?

Thanks for any help.

No user error message capability in Queries, but....

If you always want to have exactly 7 days, use:
Between [Enter Date] and DateAdd("d",7,[Enter Date])

Or use a form for parameter entry.
In which case the query criteria would be:

Between forms!FormName!StartDate and forms!FormName! EndDate

In the form you can code a command button click event:

If [EndDate] < [StartDate]+ 7 then
MsgBox "You must enter an End date 7 days later than the start date."
[EndDate].SetFocus
End If
' Then continue with whatever other code you need.

You can enter an EndDate greater than 7 days, but not less.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

.