469,945 Members | 1,858 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

Search on combination of two columns - tricky one

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?
Aug 15 '06 #1
1 1296
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"
Aug 15 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by todd | last post: by
20 posts views Thread by sophia | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.