Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18

Hi -
I have had this problem MANY times and I just don't think I have the
best solution.

I am running a parameter query to retrieve records where work was
completed between 2 dates. The "completed date" field contains both
date and time (e.g., 11/4/07 15:44:00) and does need the time for the
data to be properly recorded.

That said, is it possible to format the parameter "Between [Enter
start date] and [Enter end date]" to enable the user to enter just the
dates when prompted rather than the date and time to retrieve all
records betwen the dates?

Currently, to retrieve records completed between 4/1/08 and 4/18/08 by
entering dates only, the user must enter 4/19/08 to retrieve a record
that was completed some time during 4/18/08.

I tried adding a field to the query:
JustDate: DateValue([CallDateAndTime]) with
Between [Start date] and [End Date]

and tried
Between DateValue([Start date] and Datevalue([End Date])

Got errors - couldn't run code - too complex...

I can do it if I say between [End date] +1, but there MUST be a
"proper" way!

Thanks
sara

Re: Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18 records

Hi Sara,

Instead of using Between try this:

Dim RS As DAO.RecordSet
Set RS = CurrentDB.OpenRecordset("Select * From ... Where Date1 >= #" &
txtStartDate & "# And Date2 <= #" & txtEndDate & "#")

And then retrieve whatever data you need from the recordset object

Or if you are using a parameter Query try this:

In the StartDate field enter this:

>=Forms!yourForm!txtStartDate


in the EndDate field enter this:

<=Forms!yourForm!txtEndDate

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18 records

"sara" wrote in message
news:632b0fd3-6afd-4028-ba58-b4b45c5fc34a@u69g2000hse.google groups.com...
> Hi -
> I have had this problem MANY times and I just don't think I have the
> best solution.
>
> I am running a parameter query to retrieve records where work was
> completed between 2 dates. The "completed date" field contains both
> date and time (e.g., 11/4/07 15:44:00) and does need the time for the
> data to be properly recorded.
>
> That said, is it possible to format the parameter "Between [Enter
> start date] and [Enter end date]" to enable the user to enter just the
> dates when prompted rather than the date and time to retrieve all
> records betwen the dates?
>
> Currently, to retrieve records completed between 4/1/08 and 4/18/08 by
> entering dates only, the user must enter 4/19/08 to retrieve a record
> that was completed some time during 4/18/08.
>
> I tried adding a field to the query:
> JustDate: DateValue([CallDateAndTime]) with
> Between [Start date] and [End Date]
>
> and tried
> Between DateValue([Start date] and Datevalue([End Date])
>
> Got errors - couldn't run code - too complex...
>
> I can do it if I say between [End date] +1, but there MUST be a
> "proper" way!
>
> Thanks
> sara

Add 1 day to the entered end date. Date fields with time included are
greater than dates without time.

Between [Start Date] And DateAdd("d",1,[End Date])

Re: Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18 records

On Thu, 17 Apr 2008 12:59:31 -0700 (PDT), sara wrote:

>Hi -
>I have had this problem MANY times and I just don't think I have the
>best solution.
>
>I am running a parameter query to retrieve records where work was
>completed between 2 dates. The "completed date" field contains both
>date and time (e.g., 11/4/07 15:44:00) and does need the time for the
>data to be properly recorded.
>
>That said, is it possible to format the parameter "Between [Enter
>start date] and [Enter end date]" to enable the user to enter just the
>dates when prompted rather than the date and time to retrieve all
>records betwen the dates?
>
>Currently, to retrieve records completed between 4/1/08 and 4/18/08 by
>entering dates only, the user must enter 4/19/08 to retrieve a record
>that was completed some time during 4/18/08.
>
>I tried adding a field to the query:
>JustDate: DateValue([CallDateAndTime]) with
>Between [Start date] and [End Date]
>
>and tried
>Between DateValue([Start date] and Datevalue([End Date])
>
>Got errors - couldn't run code - too complex...
>
>I can do it if I say between [End date] +1, but there MUST be a
>"proper" way!
>
>Thanks
>sara

What does "between" mean in Access? The help file is not very helpful.
However, reading between the lines suggests that between means "greater than or
equal to " and "less than" (>= and <). That means that in Access, 'between'
means including first value but not including last value.

From a dictionary: "Thus in the sentence The bomb landed between the houses,
the houses are seen as points that define the boundaries of the area of impact
(so that we presume that none of the individual houses was hit)." To me this
is > and <.

Re: Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18

On Apr 17, 4:11=A0pm, Rich P wrote:
> Hi Sara,
>
> Instead of using Between try this:
>
> Dim RS As DAO.RecordSet
> Set RS =3D CurrentDB.OpenRecordset("Select * From ... Where Date1 >=3D #" =
&
> txtStartDate & "# And Date2 <=3D #" & txtEndDate & "#")
>
> And then retrieve whatever data you need from the recordset object
>
> Or if you are using a parameter Query try this:
>
> In the StartDate field enter this:
>
> >=3DForms!yourForm!txtStartDate
>
> in the EndDate field enter this:
>
> <=3DForms!yourForm!txtEndDate
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Thanks very much. I was thinking that it could be done without the >=3D
or whatever, but I understand it can't. I'm all set.

Re: Date as Parameter - Between 4/1 and 4/18 doesn"t inlclude 4/18

On Apr 17, 4:28=A0pm, "paii, Ron" wrote:
> "sara" wrote in message
>
> news:632b0fd3-6afd-4028-ba58-b4b45c5fc34a@u69g2000hse.google groups.com...
>
>
>
>
>
> > Hi -
> > I have had this problem MANY times and I just don't think I have the
> > best solution.
>
> > I am running a parameter query to retrieve records where work was
> > completed between 2 dates. The "completed date" field contains both
> > date and time (e.g., 11/4/07 15:44:00) and does need the time for the
> > data to be properly recorded.
>
> > That said, is it possible to format the parameter "Between [Enter
> > start date] and [Enter end date]" to enable the user to enter just the
> > dates when prompted rather than the date and time to retrieve all
> > records betwen the dates?
>
> > Currently, to retrieve records completed between 4/1/08 and 4/18/08 by
> > entering dates only, the user must enter 4/19/08 to retrieve a record
> > that was completed some time during 4/18/08.
>
> > I tried adding a field to the query:
> > JustDate: =A0DateValue([CallDateAndTime]) with
> > Between [Start date] and [End Date]
>
> > and tried
> > Between DateValue([Start date] and Datevalue([End Date])
>
> > Got errors - couldn't run code - too complex...
>
> > I can do it if I say between [End date] +1, but there MUST be a
> > "proper" way!
>
> > Thanks
> > sara
>
> Add 1 day to =A0the entered end date. Date fields with time included are
> greater than dates without time.
>
> Between [Start Date] And DateAdd("d",1,[End Date])- Hide quoted text -
>
> - Show quoted text -


Thanks very much. I was thinking that it could be done without the >=3D
or whatever, but I understand (now) it can't. I'm all set.