Date filtering

I'm looking for how do create a date filter for a simple recordset. I've
been away from programming for a couple years and I am blown away by how
much I've forgotten.

This what I have which isn't working of course...

strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
Set rsGigs= Server.CreateObject("ADODB.Recordset")
rsGigs.Open strSQL, objConn
gigFilter = "gigDateTime > #" & now & "#"
rsGigs.Filter = gigFilter

All I want to do is filter out past dates. I know this will be simple for
you guys, thanks for bearing with me. I've tried it a bunch of different
ways and all I get is arguement in conflict errors. I'm using Access by the
way.

Thanks!
John

Re: Date filtering

Do it in your query, which I assume is querying an Access database.

strSQL = "SELECT * FROM gigs WHERE gigDateTime > NOW()"

Ray at work


"John" wrote in message
news:6GxLd.11980$8H2.11592@twister.nyroc.rr.com...
> I'm looking for how do create a date filter for a simple recordset. I've
> been away from programming for a couple years and I am blown away by how
> much I've forgotten.
>
> This what I have which isn't working of course...
>
> strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
> Set rsGigs= Server.CreateObject("ADODB.Recordset")
> rsGigs.Open strSQL, objConn
> gigFilter = "gigDateTime > #" & now & "#"
> rsGigs.Filter = gigFilter
>
> All I want to do is filter out past dates. I know this will be simple for
> you guys, thanks for bearing with me. I've tried it a bunch of different
> ways and all I get is arguement in conflict errors. I'm using Access by
the
> way.
>
> Thanks!
> John
>
>

Re: Date filtering

It didn't work. Don't know why, it looked like a great idea too. I tried
formating the dates in the database too and no change. Your idea didn't
create any errors however, it just didn't filter past dates.


"Ray Costanzo [MVP]" wrote in
message news:ulXFa%239BFHA.2788@TK2MSFTNGP15.phx.gbl...
> Do it in your query, which I assume is querying an Access database.
>
> strSQL = "SELECT * FROM gigs WHERE gigDateTime > NOW()"
>
> Ray at work
>
>
> "John" wrote in message
> news:6GxLd.11980$8H2.11592@twister.nyroc.rr.com...
> > I'm looking for how do create a date filter for a simple recordset.
I've
> > been away from programming for a couple years and I am blown away by how
> > much I've forgotten.
> >
> > This what I have which isn't working of course...
> >
> > strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
> > Set rsGigs= Server.CreateObject("ADODB.Recordset")
> > rsGigs.Open strSQL, objConn
> > gigFilter = "gigDateTime > #" & now & "#"
> > rsGigs.Filter = gigFilter
> >
> > All I want to do is filter out past dates. I know this will be simple
for
> > you guys, thanks for bearing with me. I've tried it a bunch of
different
> > ways and all I get is arguement in conflict errors. I'm using Access by
> the
> > way.
> >
> > Thanks!
> > John
> >
> >
>
>

Re: Date filtering

" ... > Now() .."?
There are records in this table where gigDateTime is greater than the date
and time returned by the Now() function?? Hint, if I run Now() now, I will
get 2005-01-31 17:38. Are you really intending to compare the dates to
Now()?

Perhaps you had better show us a few sample records (just the relevant
fields) and describe what the datatype of that gigDateTime field is.

Bob Barrows
John wrote:
> It didn't work. Don't know why, it looked like a great idea too. I
> tried formating the dates in the database too and no change. Your
> idea didn't create any errors however, it just didn't filter past
> dates.
>
>
> "Ray Costanzo [MVP]" wrote
> in message news:ulXFa%239BFHA.2788@TK2MSFTNGP15.phx.gbl...
>> Do it in your query, which I assume is querying an Access database.
>>
>> strSQL = "SELECT * FROM gigs WHERE gigDateTime > NOW()"
>>
>> Ray at work
>>
>>
>> "John" wrote in message
>> news:6GxLd.11980$8H2.11592@twister.nyroc.rr.com...
>>> I'm looking for how do create a date filter for a simple recordset.
>>> I've been away from programming for a couple years and I am blown
>>> away by how much I've forgotten.
>>>
>>> This what I have which isn't working of course...
>>>
>>> strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
>>> Set rsGigs= Server.CreateObject("ADODB.Recordset")
>>> rsGigs.Open strSQL, objConn
>>> gigFilter = "gigDateTime > #" & now & "#"
>>> rsGigs.Filter = gigFilter
>>>
>>> All I want to do is filter out past dates. I know this will be
>>> simple for you guys, thanks for bearing with me. I've tried it a
>>> bunch of different ways and all I get is arguement in conflict
>>> errors. I'm using Access by the way.
>>>
>>> Thanks!
>>> John

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

Re: Date filtering

I want and dates that are in the future to appear and filter out dates
before Now. I suppose I could make ">" into ">=" since if the gig was today
this would be fine.

here a link to the page I'm working on...
http://halftonhorns.com/gigs.asp

as you'll see the top date is in the past and I need this to go away. Once
I get past this point I'm writing an app for the user to add whatever dates
they want without having to worry about dealing with past dates.

The dates you'll see are formatted using FormatDateTime to display the long
version. The dates in the db are short e.g. 1-7-05. I have NOT identified
the field in Access as DateTime format. When I did that I got more errors.
All the fields in the table I am calling are displayed on this page.

Incidentally, in the previously submitted sql string I change "now" to "#" &
now & "#" although there was no change.

Does this answer the question?

Thanks All!

"Bob Barrows [MVP]" wrote in message
news:uNTusU%23BFHA.244@TK2MSFTNGP10.phx.gbl...
> " ... > Now() .."?
> There are records in this table where gigDateTime is greater than the date
> and time returned by the Now() function?? Hint, if I run Now() now, I will
> get 2005-01-31 17:38. Are you really intending to compare the dates to
> Now()?
>
> Perhaps you had better show us a few sample records (just the relevant
> fields) and describe what the datatype of that gigDateTime field is.
>
> Bob Barrows
> John wrote:
> > It didn't work. Don't know why, it looked like a great idea too. I
> > tried formating the dates in the database too and no change. Your
> > idea didn't create any errors however, it just didn't filter past
> > dates.
> >
> >
> > "Ray Costanzo [MVP]" wrote
> > in message news:ulXFa%239BFHA.2788@TK2MSFTNGP15.phx.gbl...
> >> Do it in your query, which I assume is querying an Access database.
> >>
> >> strSQL = "SELECT * FROM gigs WHERE gigDateTime > NOW()"
> >>
> >> Ray at work
> >>
> >>
> >> "John" wrote in message
> >> news:6GxLd.11980$8H2.11592@twister.nyroc.rr.com...
> >>> I'm looking for how do create a date filter for a simple recordset.
> >>> I've been away from programming for a couple years and I am blown
> >>> away by how much I've forgotten.
> >>>
> >>> This what I have which isn't working of course...
> >>>
> >>> strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
> >>> Set rsGigs= Server.CreateObject("ADODB.Recordset")
> >>> rsGigs.Open strSQL, objConn
> >>> gigFilter = "gigDateTime > #" & now & "#"
> >>> rsGigs.Filter = gigFilter
> >>>
> >>> All I want to do is filter out past dates. I know this will be
> >>> simple for you guys, thanks for bearing with me. I've tried it a
> >>> bunch of different ways and all I get is arguement in conflict
> >>> errors. I'm using Access by the way.
> >>>
> >>> Thanks!
> >>> John
>
> --
> 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.
>
>

Re: Date filtering

sorry, the url is...

http://thehalftonhorns.com/gigs.asp

"John" wrote in message
news:fByLd.12165$8H2.1091@twister.nyroc.rr.com...
> I want and dates that are in the future to appear and filter out dates
> before Now. I suppose I could make ">" into ">=" since if the gig was
today
> this would be fine.
>
> here a link to the page I'm working on...
> http://halftonhorns.com/gigs.asp
>
> as you'll see the top date is in the past and I need this to go away.
Once
> I get past this point I'm writing an app for the user to add whatever
dates
> they want without having to worry about dealing with past dates.
>
> The dates you'll see are formatted using FormatDateTime to display the
long
> version. The dates in the db are short e.g. 1-7-05. I have NOT
identified
> the field in Access as DateTime format. When I did that I got more
errors.
> All the fields in the table I am calling are displayed on this page.
>
> Incidentally, in the previously submitted sql string I change "now" to "#"
&
> now & "#" although there was no change.
>
> Does this answer the question?
>
> Thanks All!
>
> "Bob Barrows [MVP]" wrote in message
> news:uNTusU%23BFHA.244@TK2MSFTNGP10.phx.gbl...
> > " ... > Now() .."?
> > There are records in this table where gigDateTime is greater than the
date
> > and time returned by the Now() function?? Hint, if I run Now() now, I
will
> > get 2005-01-31 17:38. Are you really intending to compare the dates to
> > Now()?
> >
> > Perhaps you had better show us a few sample records (just the relevant
> > fields) and describe what the datatype of that gigDateTime field is.
> >
> > Bob Barrows
> > John wrote:
> > > It didn't work. Don't know why, it looked like a great idea too. I
> > > tried formating the dates in the database too and no change. Your
> > > idea didn't create any errors however, it just didn't filter past
> > > dates.
> > >
> > >
> > > "Ray Costanzo [MVP]" wrote
> > > in message news:ulXFa%239BFHA.2788@TK2MSFTNGP15.phx.gbl...
> > >> Do it in your query, which I assume is querying an Access database.
> > >>
> > >> strSQL = "SELECT * FROM gigs WHERE gigDateTime > NOW()"
> > >>
> > >> Ray at work
> > >>
> > >>
> > >> "John" wrote in message
> > >> news:6GxLd.11980$8H2.11592@twister.nyroc.rr.com...
> > >>> I'm looking for how do create a date filter for a simple recordset.
> > >>> I've been away from programming for a couple years and I am blown
> > >>> away by how much I've forgotten.
> > >>>
> > >>> This what I have which isn't working of course...
> > >>>
> > >>> strSQL = "SELECT * FROM gigs ORDER BY gigDateTime ASC;"
> > >>> Set rsGigs= Server.CreateObject("ADODB.Recordset")
> > >>> rsGigs.Open strSQL, objConn
> > >>> gigFilter = "gigDateTime > #" & now & "#"
> > >>> rsGigs.Filter = gigFilter
> > >>>
> > >>> All I want to do is filter out past dates. I know this will be
> > >>> simple for you guys, thanks for bearing with me. I've tried it a
> > >>> bunch of different ways and all I get is arguement in conflict
> > >>> errors. I'm using Access by the way.
> > >>>
> > >>> Thanks!
> > >>> John
> >
> > --
> > 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.
> >
> >
>
>

Re: Date filtering

John wrote:
> I want and dates that are in the future to appear and filter out dates
> before Now. I suppose I could make ">" into ">=" since if the gig
> was today this would be fine.

No, that's not the point. I was wondering if there was a logic problem here
since you seemed to be saying you had future dates in this table. In this
case you don't seem to be concerned with the time, so you can use the Date()
function instead of the Now() function.

>
> here a link to the page I'm working on...
> http://halftonhorns.com/gigs.asp
>
> as you'll see the top date is in the past and I need this to go away.
> Once I get past this point I'm writing an app for the user to add
> whatever dates they want without having to worry about dealing with
> past dates.
>
> The dates you'll see are formatted using FormatDateTime to display
> the long version. The dates in the db are short e.g. 1-7-05.

No, they're not, unless you are storing them in a Text field.

> I have
> NOT identified the field in Access as DateTime format.

What do you mean? Is it a Text field?

> When I did
> that I got more errors. All the fields in the table I am calling are
> displayed on this page.

So what IS the datatype? Is it Text? Don't tell me about the format unless
it's a Text field. For Number and Date/Time fields, the Format property is
used ONLY in Access to control how data is displayed to and entered by users
in Access. It has no affect on how the data is stored. FYI, if it's a
Date/Time field, the data is stored as a Double number, the whole number
portion representing the number of days since the seed date, and the decimal
portion representing the time of day (.0=midnight, .5=noon). If you never
enter the time, then Access stores .0, or midnight.

If it's a Text field, then you need to convert it to Date/Time before
comparing it to Date() in your query:

strSQL = "SELECT * FROM gigs WHERE CDate(gigDateTime) > Date()"

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.

Re: Date filtering

ah HA! That did it, changing the field to a Date/Time format. It was
previously a text field. I have found in the past to be easier to keep all
fields in Access as text and leave any formatting on the ASP side of things.
Is this not a good practice?

Thanks, it worked!


"Bob Barrows [MVP]" wrote in message
news:ebrKz5%23BFHA.3236@TK2MSFTNGP15.phx.gbl...
> John wrote:
> > I want and dates that are in the future to appear and filter out dates
> > before Now. I suppose I could make ">" into ">=" since if the gig
> > was today this would be fine.
>
> No, that's not the point. I was wondering if there was a logic problem
here
> since you seemed to be saying you had future dates in this table. In this
> case you don't seem to be concerned with the time, so you can use the
Date()
> function instead of the Now() function.
>
> >
> > here a link to the page I'm working on...
> > http://halftonhorns.com/gigs.asp
> >
> > as you'll see the top date is in the past and I need this to go away.
> > Once I get past this point I'm writing an app for the user to add
> > whatever dates they want without having to worry about dealing with
> > past dates.
> >
> > The dates you'll see are formatted using FormatDateTime to display
> > the long version. The dates in the db are short e.g. 1-7-05.
>
> No, they're not, unless you are storing them in a Text field.
>
> > I have
> > NOT identified the field in Access as DateTime format.
>
> What do you mean? Is it a Text field?
>
> > When I did
> > that I got more errors. All the fields in the table I am calling are
> > displayed on this page.
>
> So what IS the datatype? Is it Text? Don't tell me about the format unless
> it's a Text field. For Number and Date/Time fields, the Format property is
> used ONLY in Access to control how data is displayed to and entered by
users
> in Access. It has no affect on how the data is stored. FYI, if it's a
> Date/Time field, the data is stored as a Double number, the whole number
> portion representing the number of days since the seed date, and the
decimal
> portion representing the time of day (.0=midnight, .5=noon). If you never
> enter the time, then Access stores .0, or midnight.
>
> If it's a Text field, then you need to convert it to Date/Time before
> comparing it to Date() in your query:
>
> strSQL = "SELECT * FROM gigs WHERE CDate(gigDateTime) > Date()"
>
> 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.
>
>

Re: Date filtering

John wrote:
> ah HA! That did it, changing the field to a Date/Time format.

There you go with that "format" word again ... :-)

Date/Time is a datatype. Long Date/Short Date are formats.

When we ask what the datatype of a field is, we want to know what it says in
the Data Type column of the column grid in your table's Design View in
Access. If it's a Number or Text field, we also need to know what's in the
Field Size box in the lower portion of the View.

> It was
> previously a text field. I have found in the past to be easier to
> keep all fields in Access as text and leave any formatting on the ASP
> side of things. Is this not a good practice?
>

Not really. As you've just found out ...
Numbers can be stored more efficiently as numbers, (less disk space). Also,
you don't have to worry about datatype conversions if you store them in
their natively typed fields. For example, date/time comparisons and
arithmetic are impossible with data contained in Text fields without
converting that data to date/time, an operation that consumes CPU and
prevents the use of any indexes you may have on the field.


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"

Re: Date filtering

"Bob Barrows [MVP]" wrote in message
news:u$8u4i$BFHA.3792@TK2MSFTNGP10.phx.gbl...
> John wrote:
> > ah HA! That did it, changing the field to a Date/Time format.
>
> There you go with that "format" word again ... :-)
>
> Date/Time is a datatype. Long Date/Short Date are formats.

Sorry...and I know this too, it's just a bad habit. I've been talking to
end-users for too long..."datatype" pushes them into the proverbial
headlights :-P

>
> When we ask what the datatype of a field is, we want to know what it says
in
> the Data Type column of the column grid in your table's Design View in
> Access. If it's a Number or Text field, we also need to know what's in the
> Field Size box in the lower portion of the View.
>
> > It was
> > previously a text field. I have found in the past to be easier to
> > keep all fields in Access as text and leave any formatting on the ASP
> > side of things. Is this not a good practice?
> >
>
> Not really. As you've just found out ...
> Numbers can be stored more efficiently as numbers, (less disk space).
Also,
> you don't have to worry about datatype conversions if you store them in
> their natively typed fields. For example, date/time comparisons and
> arithmetic are impossible with data contained in Text fields without
> converting that data to date/time, an operation that consumes CPU and
> prevents the use of any indexes you may have on the field.

this is good information, I'll be changing my approach to this.

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