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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.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.public.inetserver.asp.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 qRecordsInDateRange. In ASP, call it by:

dim rs, cn
set cn=server.createobject("adodb.connection")
cn.open "<valid connection string>"
set rs=server.createobject("adodb.recordset")
cn.qRecordsInDateRange "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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP10.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.public.inetserver.asp.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.public.inetserver.asp.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******@NOyahoo.SPAMcom> wrote in message
news:O1**************@TK2MSFTNGP12.phx.gbl...

Evertjan. wrote:

Bob Barrows [MVP] wrote on 29 mrt 2004 in
microsoft.public.inetserver.asp.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.

[Evertjan.]

Andrew wrote on 30 mrt 2004 in microsoft.public.inetserver.asp.general:

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.

... WHERE MONTH(theDate) = 3

[Not in Jet, I think]
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Bob Barrows]

Well, that's a different problem. If this is a search that will need to be
done quite often, and your table is likely to hold more than several hundred
records, I would consider a slight denormalization of your table: create a
new column that contains the month. A simple update query can be used to
populate it:

update table
set monthcolumn=month(thedate)

or

update table
set monthcolumn = datepart("m",thedate)

Put an index on the column and searches for March data will be very fast.
Unfortunately, you add the complication of needing to update this column
every time you add data to the table or update the thedate column. This is
the trade-off you need to consider: is the improved performance of the
search query worth the extra complication imposed by denormalizing your
table?

If you cannot add the column, or you determine that you don't need it, then
you have no choice but to force a table scan by using either the month() or
datepart() function on your column in the WHERE clause:

WHERE month(thedate)=3

or

WHERE datepart("m",thedate) = 3

HTH,
Bob Barrows

Andrew wrote:

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.

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

Thanks, I prefer option 1.

--
Andrew
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:OS**************@TK2MSFTNGP12.phx.gbl...

Well, that's a different problem. If this is a search that will need to be
done quite often, and your table is likely to hold more than several hundred records, I would consider a slight denormalization of your table: create a
new column that contains the month. A simple update query can be used to
populate it:

update table
set monthcolumn=month(thedate)

or

update table
set monthcolumn = datepart("m",thedate)

Put an index on the column and searches for March data will be very fast.
Unfortunately, you add the complication of needing to update this column
every time you add data to the table or update the thedate column. This is
the trade-off you need to consider: is the improved performance of the
search query worth the extra complication imposed by denormalizing your
table?

If you cannot add the column, or you determine that you don't need it, then you have no choice but to force a table scan by using either the month() or datepart() function on your column in the WHERE clause:

WHERE month(thedate)=3

or

WHERE datepart("m",thedate) = 3

HTH,
Bob Barrows

Andrew wrote:

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.

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