473,326 Members | 2,110 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
Jun 27 '08 #1
5 2492
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 ***
Jun 27 '08 #2

"sara" <sa*******@yahoo.comwrote in message
news:63**********************************@u69g2000 hse.googlegroups.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])
Jun 27 '08 #3
On Thu, 17 Apr 2008 12:59:31 -0700 (PDT), sara <sa*******@yahoo.comwrote:
>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 <.
Jun 27 '08 #4
On Apr 17, 4:11*pm, Rich P <rpng...@aol.comwrote:
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 Developersdexhttp://www.developersdex.com***
Thanks very much. I was thinking that it could be done without the >=
or whatever, but I understand it can't. I'm all set.
Jun 27 '08 #5
On Apr 17, 4:28*pm, "paii, Ron" <n...@no.comwrote:
"sara" <saraqp...@yahoo.comwrote in message

news:63**********************************@u69g2000 hse.googlegroups.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])- Hide quoted text -

- Show quoted text -

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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

15
by: Simon Brooke | last post by:
I'm investigating a bug a customer has reported in our database abstraction layer, and it's making me very unhappy. Brief summary: I have a database abstraction layer which is intended to...
10
by: Kenneth | last post by:
I have a Query that consist of a lot of different sales data, and one of the colums are different date. The date goes from 1jan2003 til 31jan2003. in this Query I only want the salesdata for...
4
by: Tony | last post by:
Hey guys, I use Google Groups quite a bit as it is an enormous wealth of information, and now I need some help. I have created a query using parameters to capture a range of date, the date is...
2
by: Sara | last post by:
I have followed instructions on the http://allenbrowne.com/tips.html for limiting a report to a date range. At the bottom there is a note that says You will end up using this form for all sorts...
8
by: JIM.H. | last post by:
Hello, I am calling a stored procedure to update my table. If one of the date on the screen left empty, I need to send date as null. Since MyDate=”” gives error in asp.net, how should I do...
10
by: John Austin | last post by:
I am migrating my first vb6 app to vb.net 2003. In the vb6 app a number of subs had optional date parameters: Sub Fred ( ... ,optional FromDate as Date = 0...) I need something like zero in...
20
by: andreas | last post by:
When I copy a vb.net project using date formats from one PC with a windows date format f.e. dd/mm/yyyy to another PC having a format yy/mm/dd then I get errors. How can I change for a while in the...
2
by: Billy | last post by:
This string is supposed to provide all records from an MDB database that match the courier and date specified in the query. I Response.Write the query and I get a date as 1/27/2007. The date...
2
by: anwest75 | last post by:
Hi, I'm not a newb to Access but have recently migrated to 2007 and suddenly my method doesn't seem to work. I'm generating a service award report where I need to show only those employees hitting a...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.