SELECTing by Date

"Evan Mobbs" <evan_mobbs@hotmail.com> wrote in message <news:3d4f7f9b$1@10.1.10.29>...

> This is probably an easy one, but I'm trying to select (using Visual Basic)
> all the records in a table (MS ACCESS) with today's date.
>
> What I have tried so far is:
> "SELECT * from mytable WHERE datefield = " & Date & ";"
>
> I get a Recordcount of 0. The date format in the table appears to be
> the same as what the 'Date' command returns.

It needs delimiters, and an unamibiguous format helps, too:

Const JetDateFmt = "\#yyyy\-mm\-dd\#;;;""null"""
Const JetTimeFmt = "\#hh\:nn\:ss\#;;;""null"""
Const JetDateTimeFmt = "\#yyyy\-mm\-dd hh\:nn\:ss\#;;;""null"""

"SELECT * from mytable WHERE datefield = " & Format$(Date, JetDateFmt) & ";"

> First question - Does the field in Access have to be specified as a
> Date/time type of field or can it be a regular text type of field?

I suppose it could, but then actually working with it could become
a real PITA.

> Next question - How would I select records that have tomorrow's date or
> the day after?

DateAdd?

"SELECT * from mytable WHERE datefield = " _
& Format$(DateAdd("d", 1, Date), JetDateFmt) & ";"

> Next question - How would you code it if you wanted to use a variable
> that stored the date you were after?
> example:
> varDate = Date()
> ..."SELECT * from mytable WHERE datefield = ? varDate ?

This is left as an exercise for the student.

--
Joe Foster <mailto:jlfoster%40znet.com> Got Thetans? <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!