filter results to display those older than 'today'?

Discussion in 'ASP General' started by supasnail, Oct 30, 2006.

  1. supasnail

    supasnail Guest

    I've been going round in circles here trying to get this to work and my
    ignorance of ASP is now showing me up.

    I want to construct a query string to allow me to filter entries to be
    displayed on a web page with respect to the date-entry contained in the
    database.

    i.e. an archived results page will show all entries where "my_date" is
    less than 'todays date'

    I have tried to do this by;

    Dim strSQL
    Dim showdate

    showdate = Date

    then...

    strSQL = "Select * FROM my_database WHERE my_date <" & showdate

    Although this does give a result, it has no actual bearing on todays
    date and wondered what strinkingly obvious this I'm doing wrong? when I
    use > i get all results displayed, when i use < I get none at all.

    thanks for any help
    supasnail, Oct 30, 2006
    #1
    1. Advertising

  2. Please tell us what kind of database you're using.

    Ray at work

    "supasnail" <> wrote in message
    news:...
    > I've been going round in circles here trying to get this to work and my
    > ignorance of ASP is now showing me up.
    >
    > I want to construct a query string to allow me to filter entries to be
    > displayed on a web page with respect to the date-entry contained in the
    > database.
    >
    > i.e. an archived results page will show all entries where "my_date" is
    > less than 'todays date'
    >
    > I have tried to do this by;
    >
    > Dim strSQL
    > Dim showdate
    >
    > showdate = Date
    >
    > then...
    >
    > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    >
    > Although this does give a result, it has no actual bearing on todays
    > date and wondered what strinkingly obvious this I'm doing wrong? when I
    > use > i get all results displayed, when i use < I get none at all.
    >
    > thanks for any help
    >
    Ray Costanzo [MVP], Oct 30, 2006
    #2
    1. Advertising

  3. supasnail

    supasnail Guest

    sorry, its an access database .mdb

    S





    Ray Costanzo [MVP] wrote:
    > Please tell us what kind of database you're using.
    >
    > Ray at work
    >
    > "supasnail" <> wrote in message
    > news:...
    > > I've been going round in circles here trying to get this to work and my
    > > ignorance of ASP is now showing me up.
    > >
    > > I want to construct a query string to allow me to filter entries to be
    > > displayed on a web page with respect to the date-entry contained in the
    > > database.
    > >
    > > i.e. an archived results page will show all entries where "my_date" is
    > > less than 'todays date'
    > >
    > > I have tried to do this by;
    > >
    > > Dim strSQL
    > > Dim showdate
    > >
    > > showdate = Date
    > >
    > > then...
    > >
    > > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    > >
    > > Although this does give a result, it has no actual bearing on todays
    > > date and wondered what strinkingly obvious this I'm doing wrong? when I
    > > use > i get all results displayed, when i use < I get none at all.
    > >
    > > thanks for any help
    > >
    supasnail, Oct 30, 2006
    #3
  4. Format the date accordingly then, YYYY-MM-DD. See here:
    http://www.aspfaq.com/show.asp?id=2023

    Sample:

    Function dtForAccess(d)
    dtForAccess = Year(d) & "-" & Right("0" & Month(d), 2) & "-" & Right("0"
    & Day(d), 2)
    End Function

    Ray at work


    "supasnail" <> wrote in message
    news:...
    > sorry, its an access database .mdb
    >
    > S
    >
    >
    >
    >
    >
    > Ray Costanzo [MVP] wrote:
    >> Please tell us what kind of database you're using.
    >>
    >> Ray at work
    >>
    >> "supasnail" <> wrote in message
    >> news:...
    >> > I've been going round in circles here trying to get this to work and my
    >> > ignorance of ASP is now showing me up.
    >> >
    >> > I want to construct a query string to allow me to filter entries to be
    >> > displayed on a web page with respect to the date-entry contained in the
    >> > database.
    >> >
    >> > i.e. an archived results page will show all entries where "my_date" is
    >> > less than 'todays date'
    >> >
    >> > I have tried to do this by;
    >> >
    >> > Dim strSQL
    >> > Dim showdate
    >> >
    >> > showdate = Date
    >> >
    >> > then...
    >> >
    >> > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    >> >
    >> > Although this does give a result, it has no actual bearing on todays
    >> > date and wondered what strinkingly obvious this I'm doing wrong? when I
    >> > use > i get all results displayed, when i use < I get none at all.
    >> >
    >> > thanks for any help
    >> >

    >
    Ray Costanzo [MVP], Oct 30, 2006
    #4
  5. supasnail

    supasnail Guest

    I don't know if it makes any difference but I have the database field
    set to date/time, and "short Date" and am using a
    <%session.lcid=1046%> on the results page ( I know this is really for
    Portugal but the 2057 for UK never displays as UK date format should,
    not for me anyway.)
    The Access database can be written to and read from happily and
    displays the date correctly. I just dont seem to be able to work out
    how to filter with respect to todays date.

    S



    An Access
    supasnail wrote:
    > sorry, its an access database .mdb
    >
    > S
    >
    >
    >
    >
    >
    > Ray Costanzo [MVP] wrote:
    > > Please tell us what kind of database you're using.
    > >
    > > Ray at work
    > >
    > > "supasnail" <> wrote in message
    > > news:...
    > > > I've been going round in circles here trying to get this to work and my
    > > > ignorance of ASP is now showing me up.
    > > >
    > > > I want to construct a query string to allow me to filter entries to be
    > > > displayed on a web page with respect to the date-entry contained in the
    > > > database.
    > > >
    > > > i.e. an archived results page will show all entries where "my_date" is
    > > > less than 'todays date'
    > > >
    > > > I have tried to do this by;
    > > >
    > > > Dim strSQL
    > > > Dim showdate
    > > >
    > > > showdate = Date
    > > >
    > > > then...
    > > >
    > > > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    > > >
    > > > Although this does give a result, it has no actual bearing on todays
    > > > date and wondered what strinkingly obvious this I'm doing wrong? when I
    > > > use > i get all results displayed, when i use < I get none at all.
    > > >
    > > > thanks for any help
    > > >
    supasnail, Oct 30, 2006
    #5
  6. supasnail wrote:
    > I've been going round in circles here trying to get this to work and
    > my ignorance of ASP is now showing me up.
    >
    > I want to construct a query string to allow me to filter entries to be
    > displayed on a web page with respect to the date-entry contained in
    > the database.
    >
    > i.e. an archived results page will show all entries where "my_date" is
    > less than 'todays date'
    >
    > I have tried to do this by;
    >
    > Dim strSQL
    > Dim showdate
    >
    > showdate = Date
    >
    > then...
    >
    > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    >
    > Although this does give a result, it has no actual bearing on todays
    > date and wondered what strinkingly obvious this I'm doing wrong? when
    > I use > i get all results displayed, when i use < I get none at all.
    >
    > thanks for any help

    If you want today's date, then use the Date() function:

    strSQL = "... WHERE my_date < Date"


    --
    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.
    Bob Barrows [MVP], Oct 30, 2006
    #6
  7. supasnail

    supasnail Guest

    Thanks Bob, thats perfect - I was on the right lines but you've allowed
    me to put everything in the right place lol. Now,... on to my next
    headache lol

    thank you very much

    S



    Bob Barrows [MVP] wrote:
    > supasnail wrote:
    > > I've been going round in circles here trying to get this to work and
    > > my ignorance of ASP is now showing me up.
    > >
    > > I want to construct a query string to allow me to filter entries to be
    > > displayed on a web page with respect to the date-entry contained in
    > > the database.
    > >
    > > i.e. an archived results page will show all entries where "my_date" is
    > > less than 'todays date'
    > >
    > > I have tried to do this by;
    > >
    > > Dim strSQL
    > > Dim showdate
    > >
    > > showdate = Date
    > >
    > > then...
    > >
    > > strSQL = "Select * FROM my_database WHERE my_date <" & showdate
    > >
    > > Although this does give a result, it has no actual bearing on todays
    > > date and wondered what strinkingly obvious this I'm doing wrong? when
    > > I use > i get all results displayed, when i use < I get none at all.
    > >
    > > thanks for any help

    > If you want today's date, then use the Date() function:
    >
    > strSQL = "... WHERE my_date < Date"
    >
    >
    > --
    > 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.
    supasnail, Oct 30, 2006
    #7
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Prime
    Replies:
    6
    Views:
    1,928
  2. Shiperton Henethe
    Replies:
    1
    Views:
    318
    brucie
    Oct 17, 2003
  3. BinnuChowdary
    Replies:
    1
    Views:
    536
    Swanand Mokashi
    May 1, 2006
  4. BinnuChowdary
    Replies:
    0
    Views:
    411
    BinnuChowdary
    May 2, 2006
  5. BinnuChowdary
    Replies:
    1
    Views:
    546
    =?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=
    May 2, 2006
Loading...

Share This Page