Search on combination of two columns - tricky one

Discussion in 'ASP General' started by JP SIngh, Aug 15, 2006.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    I have an ASP page where I am building a query to get the data from the
    table. I am using Microsoft Access and ASP

    The table has three columns Id, StartDate, EndDate.

    For each record the users only enter either the startdate or enddate and not
    both.

    When i am writing my querystring I want to sort my results by date but the
    date should be a combination of StartDate, EndDate

    I tried something like this

    SELECT * FROM Table ORDER BY NZ(StartDate,EndDate)

    This works in the database directly but on the ASP page it does not allow
    the NZ expression.

    Can any one help please?
    JP SIngh, Aug 15, 2006
    #1
    1. Advertising

  2. JP SIngh wrote:
    > Hi All
    >
    > I have an ASP page where I am building a query to get the data from
    > the table. I am using Microsoft Access and ASP
    >
    > The table has three columns Id, StartDate, EndDate.
    >
    > For each record the users only enter either the startdate or enddate
    > and not both.
    >
    > When i am writing my querystring I want to sort my results by date
    > but the date should be a combination of StartDate, EndDate
    >
    > I tried something like this
    >
    > SELECT * FROM Table ORDER BY NZ(StartDate,EndDate)
    >
    > This works in the database directly but on the ASP page it does not
    > allow the NZ expression.
    >
    > Can any one help please?


    You have to use the IIF function instead:

    ORDER BY iif(IsNull(StartDate),EndDate,StartDate)

    --
    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], Aug 15, 2006
    #2
    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. news.frontiernet.net
    Replies:
    6
    Views:
    1,109
    news.frontiernet.net
    Apr 16, 2004
  2. Replies:
    9
    Views:
    523
    CBFalconer
    Apr 25, 2006
  3. Darcy
    Replies:
    6
    Views:
    165
    Dominique
    May 6, 2004
  4. Abby Lee
    Replies:
    5
    Views:
    381
    Abby Lee
    Aug 2, 2004
  5. Josselin
    Replies:
    0
    Views:
    98
    Josselin
    Sep 24, 2007
Loading...

Share This Page