|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
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 <tjbf915fjq1hsf43ql2r5k376215of8***@4ax.com>, Martin
<martinval***@comcast.net> writes >In my VB6 program, I'm executing a query against an MS Access database WHERE MyDate BETWEEN #2005-05-27 00:00:00# AND #2005-05-27 23:59:59#>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 E-Mail: newsgroups@NOSPAMzadsoft.com ZAD Software Systems Web : www.zadsoft.com On Sat, 28 May 2005 01:30:29 +0100, "Andrew D. Newbould"
<newsgro***@NOzadSPANsoft.com> wrote: Show quoteHide quote >In message <tjbf915fjq1hsf43ql2r5k376215of8***@4ax.com>, Martin Thanks, that works.><martinval***@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# 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)) Show quoteHide quote "Martin" <martinval***@comcast.net> wrote in message news:3gqg91da8utg352t1e93375sndcv1kjnoa@4ax.com... > On Sat, 28 May 2005 01:30:29 +0100, "Andrew D. Newbould" > <newsgro***@NOzadSPANsoft.com> wrote: > > >In message <tjbf915fjq1hsf43ql2r5k376215of8***@4ax.com>, Martin > ><martinval***@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# > > 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: Show quoteHide quote >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" <martinval***@comcast.net> wrote in message >news:3gqg91da8utg352t1e93375sndcv1kjnoa@4ax.com... >> On Sat, 28 May 2005 01:30:29 +0100, "Andrew D. Newbould" >> <newsgro***@NOzadSPANsoft.com> wrote: >> >> >In message <tjbf915fjq1hsf43ql2r5k376215of8***@4ax.com>, Martin >> ><martinval***@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# >> >> 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" <martinval***@comcast.net> wrote in message Have you tried the Format function?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# Format(MyDate, "mm/dd/yy") 
Other interesting topics
simple Excel question
Failed to load control...Your version of ocx may be outdated.. Folder loading problem. Support Classic VB Current User Package/Deployment Wizard how to pass over events in stacked objects? Set Scroll position in a list box Can I develop VB6 App for windows CE? VB6 and HTMLHelp crash |
|||||||||||||||||||||||
