date range

[Andrew]

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks
--
Andrew

[Bob Barrows]

Andrew wrote:

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks

Jet requires you to use # to delimit literal dates. You should use ISO date
format, like this:

where date >=#2004-03-01# and date <=#2004-03-31#
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Andrew]

But Access datetime format only has 3/31/2004, how can I make it work?
Thanks

--
Andrew
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Andrew wrote:

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks

Jet requires you to use # to delimit literal dates. You should use ISO

date format, like this:

where date >=#2004-03-01# and date <=#2004-03-31#
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Andrew]

What I am going to do is to select the month or year from the date, for
example,
select * from table where thedate >=#2004-[var]-01# and thedate
<=#2004-[var]-31#
That means all the record in a month. Can you help? Thanks.

--
Andrew
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Andrew wrote:

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks

Jet requires you to use # to delimit literal dates. You should use ISO

date format, like this:

where date >=#2004-03-01# and date <=#2004-03-31#
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

You are wrong. The ISO format I suggested will work fine. I have used it
myself.

Your error is in thinking that the database stores the dates in a particular
format. This is not true. If you read the online help, you will see that
Access stores date/time data as numbers of type Double. The whole number
portion represents the number of days since the seed date. The decimal
portion represents the time of day (.0 = midnight, .5 = noon)

Bob Barrows
Andrew wrote:

But Access datetime format only has 3/31/2004, how can I make it work?
Thanks
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Andrew wrote:

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks

Jet requires you to use # to delimit literal dates. You should use
ISO date format, like this:

where date >=#2004-03-01# and date <=#2004-03-31#

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Andrew wrote on 29 mrt 2004 in microsoft.publi c.inetserver.as p.general:

What I am going to do is to select the month or year from the date, for
example,
select * from table where thedate >=#2004-[var]-01# and thedate
<=#2004-[var]-31#
That means all the record in a month. Can you help? Thanks.

What about February, or April for that matter?
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

That makes no sense. What you should do is pass a start and end date to the
query, like this:

select <list of fields> from table
where thedate >= [pStart] and theDate <=[pEnd]

When you test this in Access, you will get prompted for the two dates. Save
the query as qRecordsInDateR ange. In ASP, call it by:

dim rs, cn
set cn=server.creat eobject("adodb. connection")
cn.open "<valid connection string>"
set rs=server.creat eobject("adodb. recordset")
cn.qRecordsInDa teRange "2004-03-01#,#2004-03-31#,rs

HTH,
Bob Barrows
Andrew wrote:

What I am going to do is to select the month or year from the date,
for example,
select * from table where thedate >=#2004-[var]-01# and thedate
<=#2004-[var]-31#
That means all the record in a month. Can you help? Thanks.
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Andrew wrote:

Hi,
I select from an Access table " where date >= '3/1/2004' and date
<='3/31/2004' ", but no record. Can you tell me how to make this
comparission?
Thanks

Jet requires you to use # to delimit literal dates. You should use
ISO date format, like this:

where date >=#2004-03-01# and date <=#2004-03-31#
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so
I don't check it very often. If you must reply off-line, then remove
the "NO SPAM"

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Evertjan.]

Bob Barrows [MVP] wrote on 29 mrt 2004 in
microsoft.publi c.inetserver.as p.general:

That makes no sense. What you should do is pass a start and end date
to the query, like this:

select <list of fields> from table
where thedate >= [pStart] and theDate <=[pEnd]

What about the "between" keyword ?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows [MVP]]

Evertjan. wrote:

Bob Barrows [MVP] wrote on 29 mrt 2004 in
microsoft.publi c.inetserver.as p.general:

That makes no sense. What you should do is pass a start and end date
to the query, like this:

select <list of fields> from table
where thedate >= [pStart] and theDate <=[pEnd]

What about the "between" keyword ?

Either way.
Aaron has lobbied against the use of "between", and while I personally have
no problem with it, other users might. Since the OP started off using this
method, I saw no reason to suggest otherwise.

One thing I neglected to suggest to the OP: if users are entering times as
well as dates into thedate, then the above query should be changed to:

select <list of fields> from table
where thedate >= [pStart] and theDate <dateadd("d", 1,[pEnd])

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Andrew]

How can I select the records containing March(or 03) in theDate, for
example, 03/21/2004, 03/12/1996. Can I do that? Thanks anyway.

--
Andrew
"Bob Barrows [MVP]" <re******@NOyah oo.SPAMcom> wrote in message
news:O1******** ******@TK2MSFTN GP12.phx.gbl...

Evertjan. wrote:

Bob Barrows [MVP] wrote on 29 mrt 2004 in
microsoft.publi c.inetserver.as p.general:

That makes no sense. What you should do is pass a start and end date
to the query, like this:

select <list of fields> from table
where thedate >= [pStart] and theDate <=[pEnd]

What about the "between" keyword ?

Either way.
Aaron has lobbied against the use of "between", and while I personally

have no problem with it, other users might. Since the OP started off using this
method, I saw no reason to suggest otherwise.

One thing I neglected to suggest to the OP: if users are entering times as
well as dates into thedate, then the above query should be changed to:

select <list of fields> from table
where thedate >= [pStart] and theDate <dateadd("d", 1,[pEnd])

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.