Home > Archive > Visual Basic > May 2005 > How to query for "today's" records?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
How to query for "today's" records?
|
|
| Martin 2005-05-28, 3:55 am |
| In my VB6 program, I'm executing a query against an MS Access database
in which I want to return all records which have a specified date in a
date field. The problem is, if I say: ...WHERE MyDate = #5/27/05# then
I get no records because the date field contains the date AND the time
and thus is not exactly equal to #5/27/05#.
How can I write the WHERE clause so that I get all of the day's
records no matter what the time on them is?
Thanks.
| |
| Andrew D. Newbould 2005-05-28, 3:55 am |
| In message <tjbf915fjq1hsf43ql2r5k376215of8o83@4ax.com>, Martin
<martinvalley@comcast.net> writes
>In my VB6 program, I'm executing a query against an MS Access database
>in which I want to return all records which have a specified date in a
>date field. The problem is, if I say: ...WHERE MyDate = #5/27/05# then
>I get no records because the date field contains the date AND the time
>and thus is not exactly equal to #5/27/05#.
>
>How can I write the WHERE clause so that I get all of the day's
>records no matter what the time on them is?
>
>Thanks.
WHERE MyDate BETWEEN #2005-05-27 00:00:00# AND #2005-05-27 23:59:59#
--
Andrew D. Newbould E-Mail: newsgroups@NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
| |
| Martin 2005-05-28, 3:55 pm |
| On Sat, 28 May 2005 01:30:29 +0100, "Andrew D. Newbould"
<newsgroups@NOzadSPANsoft.com> wrote:
>In message <tjbf915fjq1hsf43ql2r5k376215of8o83@4ax.com>, Martin
><martinvalley@comcast.net> writes
>
>WHERE MyDate BETWEEN #2005-05-27 00:00:00# AND #2005-05-27 23:59:59#
Thanks, that works.
But, I thought maybe there was a built-in function that returned just
the date portion of the date/time value. No?
| |
| Norm Cook 2005-05-28, 3:55 pm |
| Here's one way:
Dim d As Date
d = #5/27/2005 11:59:58 PM#
Debug.Print DateSerial(Year(d), Month(d), Day(d))
Debug.Print TimeSerial(Hour(d), Minute(d), Second(d))
"Martin" <martinvalley@comcast.net> wrote in message
news:3gqg91da8utg352t1e93375sndcv1kjnoa@
4ax.com...
> On Sat, 28 May 2005 01:30:29 +0100, "Andrew D. Newbould"
> <newsgroups@NOzadSPANsoft.com> wrote:
>
>
> Thanks, that works.
>
> But, I thought maybe there was a built-in function that returned just
> the date portion of the date/time value. No?
| |
| Martin 2005-05-29, 3:55 pm |
| I was thinking more along the line of a function that would extract
the date part of the value stored in the MDB record which could then
be compared to a given date string. Like:
WHERE TheDateOnly(MyDate) = #5/27/05#
I looked in to the DatePart function but it seems to return either the
Month, the Day or the Year (but not a combination of the three).
Oh well, the original suggestion does what I needed to do.
Martin
On Sat, 28 May 2005 10:04:54 -0500, "Norm Cook"
<normcookNOSPAM@cableone.net> wrote:
>Here's one way:
> Dim d As Date
> d = #5/27/2005 11:59:58 PM#
> Debug.Print DateSerial(Year(d), Month(d), Day(d))
> Debug.Print TimeSerial(Hour(d), Minute(d), Second(d))
>
>"Martin" <martinvalley@comcast.net> wrote in message
> news:3gqg91da8utg352t1e93375sndcv1kjnoa@
4ax.com...
>
| |
| John Cleveland 2005-05-29, 3:55 pm |
| "Martin" <martinvalley@comcast.net> wrote in message
news:rnoj9115j9kkqmflevn84ct8h9b55a6air@
4ax.com...
>I was thinking more along the line of a function that would extract
> the date part of the value stored in the MDB record which could then
> be compared to a given date string. Like:
>
> WHERE TheDateOnly(MyDate) = #5/27/05#
Have you tried the Format function?
Format(MyDate, "mm/dd/yy")
|
|
|
|
|