How to query for "today's" records?

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.

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

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?

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?

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... 
>

"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")