Search on combination of two columns - tricky one

[JP SIngh]

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?

 

[Bob Barrows [MVP]]

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)