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]

Re: date range

Andrew wrote:[color=blue]
> 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[/color]


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]

Re: date range

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

--
Andrew


"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:%23dJl1oYF EHA.3188@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Andrew wrote:[color=green]
> > 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[/color]
>
>
> Jet requires you to use # to delimit literal dates. You should use ISO[/color]
date[color=blue]
> 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"
>
>[/color]

[Andrew]

Re: date range

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" <reb01501@NOyah oo.SPAMcom> wrote in message
news:%23dJl1oYF EHA.3188@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Andrew wrote:[color=green]
> > 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[/color]
>
>
> Jet requires you to use # to delimit literal dates. You should use ISO[/color]
date[color=blue]
> 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"
>
>[/color]

[Bob Barrows [MVP]]

Re: date range

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:[color=blue]
> But Access datetime format only has 3/31/2004, how can I make it work?
> Thanks
>
>
> "Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:%23dJl1oYF EHA.3188@TK2MSF TNGP10.phx.gbl. ..[color=green]
>> Andrew wrote:[color=darkred]
>>> 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[/color]
>>
>>
>> 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#
>>[/color][/color]


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

Re: date range

Andrew wrote on 29 mrt 2004 in microsoft.publi c.inetserver.as p.general:
[color=blue]
> 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.[/color]

What about February, or April for that matter?


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

[Bob Barrows [MVP]]

Re: date range

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:[color=blue]
> 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" <reb01501@NOyah oo.SPAMcom> wrote in message
> news:%23dJl1oYF EHA.3188@TK2MSF TNGP10.phx.gbl. ..[color=green]
>> Andrew wrote:[color=darkred]
>>> 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[/color]
>>
>>
>> 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"[/color][/color]

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

Re: date range

Bob Barrows [MVP] wrote on 29 mrt 2004 in
microsoft.publi c.inetserver.as p.general:
[color=blue]
> 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]
>[/color]

What about the "between" keyword ?

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

[Bob Barrows [MVP]]

Re: date range

Evertjan. wrote:[color=blue]
> Bob Barrows [MVP] wrote on 29 mrt 2004 in
> microsoft.publi c.inetserver.as p.general:
>[color=green]
>> 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]
>>[/color]
>
> What about the "between" keyword ?[/color]
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]

Re: date range

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]" <reb01501@NOyah oo.SPAMcom> wrote in message
news:O1RLZzaFEH A.2600@TK2MSFTN GP12.phx.gbl...[color=blue]
> Evertjan. wrote:[color=green]
> > Bob Barrows [MVP] wrote on 29 mrt 2004 in
> > microsoft.publi c.inetserver.as p.general:
> >[color=darkred]
> >> 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]
> >>[/color]
> >
> > What about the "between" keyword ?[/color]
> Either way.
> Aaron has lobbied against the use of "between", and while I personally[/color]
have[color=blue]
> 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.
>
>[/color]

[Evertjan.]

Re: date range

Andrew wrote on 30 mrt 2004 in microsoft.publi c.inetserver.as p.general:
[color=blue]
> 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.[/color]


... WHERE MONTH(theDate) = 3

[Not in Jet, I think]


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

[Bob Barrows]

Re: date range

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=mon th(thedate)

or

update table
set monthcolumn = datepart("m",th edate)

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",th edate) = 3

HTH,
Bob Barrows

Andrew wrote:[color=blue]
> 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.
>[/color]



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

Re: date range

Thanks, I prefer option 1.

--
Andrew


"Bob Barrows" <reb01501@NOyah oo.SPAMcom> wrote in message
news:OSYOu9kFEH A.1272@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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[/color]
hundred[color=blue]
> 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=mon th(thedate)
>
> or
>
> update table
> set monthcolumn = datepart("m",th edate)
>
> 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,[/color]
then[color=blue]
> you have no choice but to force a table scan by using either the month()[/color]
or[color=blue]
> datepart() function on your column in the WHERE clause:
>
> WHERE month(thedate)= 3
>
> or
>
> WHERE datepart("m",th edate) = 3
>
> HTH,
> Bob Barrows
>
> Andrew wrote:[color=green]
> > 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.
> >[/color]
>
>
>
> --
> 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"
>
>[/color]