473,241 Members | 1,554 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,241 software developers and data experts.

Date/Time Field query

I have a field type Date/Time that automatically defaults to Now().
I'm having a problem writing a query that will retrieve all the
records for a specific date the user to enters when prompted. I could
retrieve all the records for a specific date if I hard-code the date
into the query, but not if I want to prompt the user. Has anybody
else experienced this? Does anybody have any suggestions?
Nov 12 '05 #1
5 4260
On 20 Jan 2004 11:20:41 -0800, ja*****@4link.net (Javier) wrote:
I have a field type Date/Time that automatically defaults to Now().
I'm having a problem writing a query that will retrieve all the
records for a specific date the user to enters when prompted. I could
retrieve all the records for a specific date if I hard-code the date
into the query, but not if I want to prompt the user. Has anybody
else experienced this? Does anybody have any suggestions?


The best way to handle this is to query all values greater than or equal to
the date in question, and less than the following day. Unlike other possible
techniques such as using DatePart(), queries built this way can be optimized
when there is an index on the date field.
Nov 12 '05 #2
ja*****@4link.net (Javier) wrote in
<d3*************************@posting.google.com> :
I have a field type Date/Time that automatically defaults to
Now(). I'm having a problem writing a query that will retrieve all
the records for a specific date the user to enters when prompted.
I could retrieve all the records for a specific date if I
hard-code the date into the query, but not if I want to prompt the
user. Has anybody else experienced this? Does anybody have any
suggestions?
I have always felt that combining date and time into a single field
causes too many problems, so if I were storing date and time, I'd
have one field for Date() with that as the default value and
another field for the time with Time() as the default value.

If you combine the fields, then I'd use:
=#1/1/2004# And <#1/2/2004#


to get all records for 1/1/2004.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #3
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90.. .
ja*****@4link.net (Javier) wrote in
<d3*************************@posting.google.com> :
I have a field type Date/Time that automatically defaults to
Now(). I'm having a problem writing a query that will retrieve all
the records for a specific date the user to enters when prompted.
I could retrieve all the records for a specific date if I
hard-code the date into the query, but not if I want to prompt the
user. Has anybody else experienced this? Does anybody have any
suggestions?


I have always felt that combining date and time into a single field
causes too many problems, so if I were storing date and time, I'd
have one field for Date() with that as the default value and
another field for the time with Time() as the default value.

If you combine the fields, then I'd use:
>=#1/1/2004# And <#1/2/2004#


to get all records for 1/1/2004.


Odd. I've always felt that combining them into a single field is the only
sensible thing to do! If, for example, you've got prices that vary not only
by day but by time, and you want to know the price at a particular
date/time, it's a very messy query unless they're combined. You can always
use DateValue and TimeValue to create computed fields in a query to separate
them if you really need them that way.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)


Nov 12 '05 #4
date() might work better.
Now() includes the time so you may be missing records that are the
same day but another time.
HTH
Pachydermitis

ja*****@4link.net (Javier) wrote in message news:<d3*************************@posting.google.c om>...
I have a field type Date/Time that automatically defaults to Now().
I'm having a problem writing a query that will retrieve all the
records for a specific date the user to enters when prompted. I could
retrieve all the records for a specific date if I hard-code the date
into the query, but not if I want to prompt the user. Has anybody
else experienced this? Does anybody have any suggestions?

Nov 12 '05 #5
NOSPAM_djsteele@NOSPAM_canada.com (Douglas J. Steele) wrote in
<Ir********************@news04.bloor.is.net.cable. rogers.com>:
"David W. Fenton" <dX********@bway.net.invalid> wrote in message
news:94***************************@24.168.128.90. ..
ja*****@4link.net (Javier) wrote in
<d3*************************@posting.google.com> :
>I have a field type Date/Time that automatically defaults to
>Now(). I'm having a problem writing a query that will retrieve
>all the records for a specific date the user to enters when
>prompted. I could retrieve all the records for a specific date
>if I hard-code the date into the query, but not if I want to
>prompt the user. Has anybody else experienced this? Does
>anybody have any suggestions?


I have always felt that combining date and time into a single
field causes too many problems, so if I were storing date and
time, I'd have one field for Date() with that as the default
value and another field for the time with Time() as the default
value.

If you combine the fields, then I'd use:
>=#1/1/2004# And <#1/2/2004#


to get all records for 1/1/2004.


Odd. I've always felt that combining them into a single field is
the only sensible thing to do! If, for example, you've got prices
that vary not only by day but by time, and you want to know the
price at a particular date/time, it's a very messy query unless
they're combined. You can always use DateValue and TimeValue to
create computed fields in a query to separate them if you really
need them that way.


But selects on those will not utilize indexes because you'll be
selecting on a calculated value. The criteria above will use the
indexes, and, of course, you can separate out the date and time
parts for display and printing.

But I think that dates and times are different kinds of data in
many applications, and don't belong in the same field.

Of course, that depends on your application -- whether there is
advantage to combining or splitting will entirely depend on what
the data means in your particular schema and how it is used.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 12 '05 #6

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

Similar topics

7
by: vnl | last post by:
I'm trying to run a SQL query but can't find any records when trying to select a certain date. Here's the sql: SELECT field 1, field2, date_and_time, FROM table1 WHERE date_and_time =...
1
by: Thomas Bartkus | last post by:
If we have a date/time field and are doing frequent queries WHERE {date/time field} BETWEEN TimeA AND TimeB Does it make sense, query speed wise, to create an index on the date/time field? ...
7
by: Nicolae Fieraru | last post by:
Hi All, I have a table tblProducts where I have four fields:\ Index, ProductName, EnterDate (as Date/Time - Medium Date), PurchaseDate (Date/Time - Medium Date) The EnterDate is automatically...
4
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following...
5
by: Macca | last post by:
Hi, I have a table which has a date/time field. I am storing them as follows :- 01/01/2005 11:25 01/01/2005 19:44 02/01/2005 05:04
11
by: Dixie | last post by:
How can I programatically, take some Date/Time fields present in a table in the current database and change their type to text? dixie
2
by: x | last post by:
hi i am a pilot by profession. i want to create a database of my logbook using ms access 2002. i am facing a problem regarding the format of time field. when i select "Data/Time" data type for my...
3
by: Jim in Arizona | last post by:
I have a gridview that's being populated from an access db query. The problem I'm having is that the date/time fields in access that are populating the gridview are showing both date and time, when...
2
by: john | last post by:
From an Excel-import I have an access table with a datetime field of wich both date and time are entered. I've set the field's notation properties to Short Date. When I view the table in tableview...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...

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.