By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,686 Members | 2,770 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,686 IT Pros & Developers. It's quick & easy.

Date/Time Field query

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
"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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.