Criteria for date diff

[margaret hannah]

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.

 

[Ken Snell [MVP]]

If you know that the ending date should be 7 days from the starting date,
then don't ask the user for the ending date. Just ask for the starting date
and then let the query calculate the ending date to be used. Use something
like this as your criterion expression:

Between [Enter the starting date:] And [Enter the starting date:] + 7

 

[fredg]

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.

 

[Guest]

I've tried the suggestion and even tried different
variations and still can't get it to work. What you
suggest makes sense, but I can't get the expression
correct. Is there a SQL statement that I could use?

-----Original Message-----
If you know that the ending date should be 7 days from the starting date,
then don't ask the user for the ending date. Just ask for the starting date
and then let the query calculate the ending date to be used. Use something
like this as your criterion expression:

Between [Enter the starting date:] And [Enter the starting date:] + 7
--

Ken Snell
<MS ACCESS MVP>

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.

.

 

[Guest]

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.
.

 

[Ken Snell [MVP]]

It appears that fredg's suggestion was successful for you. Good luck!

--

Ken Snell
<MS ACCESS MVP>

I've tried the suggestion and even tried different
variations and still can't get it to work. What you
suggest makes sense, but I can't get the expression
correct. Is there a SQL statement that I could use?

-----Original Message-----
If you know that the ending date should be 7 days from the starting date,
then don't ask the user for the ending date. Just ask for the starting date
and then let the query calculate the ending date to be used. Use something
like this as your criterion expression:

Between [Enter the starting date:] And [Enter the starting date:] + 7
--

Ken Snell
<MS ACCESS MVP>

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.

.

 

[fredg]

It appears that fredg's suggestion was successful for you. Good luck!

Ken,
I've found that using the same parameter and adding days to it, such
as
Between [Enter the starting date:] And [Enter the starting date:] + 7
will give a too complex to be evaluated etc. error UNLESS the
parameter has first been entered into the Query Parameter dialog box
as Date/Time datatype.

 

[Ken Snell [MVP]]

Thanks for that info, fredg. I've added it to my treasure trove of ACCESS
info!

--

Ken Snell
<MS ACCESS MVP>

It appears that fredg's suggestion was successful for you. Good luck!

Ken,
I've found that using the same parameter and adding days to it, such
as
Between [Enter the starting date:] And [Enter the starting date:] + 7
will give a too complex to be evaluated etc. error UNLESS the
parameter has first been entered into the Query Parameter dialog box
as Date/Time datatype.