Filtering a recordset in ASP

[Square Eye]

Hello

I'd like to do something like this - first define my Recordset as:

SELECT Name, BirthYear, (year(now()) - BirthYear) AS Age FROM Table

And then in my page I'd like to use a recordset filter to display two different lists, one of over-50s, and one of under-50s, so say something like this:

Recordset.Filter = "Age > 50"
Do while not Recordset.eof
Response.Write (Recordset("Name"))
Recordset.MoveNext
loop
Recordset.Filter = 0

and then the second one (Filter="Age < 50")

However this whole technique works if I was to filter by e.g. "BirthYear > 1950", but not if I try and use my own defined column label (Age).

Is there a way of filtering using a calculation, so that I can tie it into today's date? Or do I have to just define two separate recordsets and make the calculation at that stage?

Many thanks if you can help!

Square

[Square Eye]

<deleted>

[Newsgroup_User]

You realize that you're incorrect, right?
2007 - 1983 is 24, yet my baby brother remains stubbornly 23. ;)

That being said, I thought that client side filters would work on a named
expression column. If that's not the case (admittedly, I don't use client
side filtering), then move the filter to your query. There, you'll have to
copy the calculation into the where clause as it will not recognize your
expression as a valid column, either.



"Square Eye" <contact@squareeye.com> wrote in message
news:f00fc6$hla$1@forums.macromedia.com...
> Hello
>
> I'd like to do something like this - first define my Recordset as:
>
> SELECT Name, BirthYear, (BirthYear - (year(now))) AS Age FROM Table
>
> And then in my page I'd like to use a recordset filter to display two
> different lists, one of over-50s, and one of under-50s, so say something
> like
> this:
>
> Recordset.Filter = "Age > 50"
> Do while not Recordset.eof
> Response.Write (Recordset("Name"))
> Recordset.MoveNext
> loop
> Recordset.Filter = 0
>
> and then the second one (Filter="Age < 50")
>
> However this whole techniques if I was to filter by e.g. "BirthYear >
> 1950",
> but not if I try and use my own defined column label (Age).
>
> Is there a way of filtering using a calculation, so that I can tie it into
> today's date? Or do I have to just define two separate recordsets and
> make the
> calculation at that stage?
>
> Many thanks if you can help!
>
> Square
>

[Newsgroup_User]

YOu can't filter by alias names. You could have something like

SELECT Name, BirthYear, (BirthYear - (year(now))) AS Age FROM Table where
(BirthYear - (year(now))) > 50

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

[Square Eye]

Thanks. I wanted to avoid doing the calculation in the recordset, if possible, because I wanted to use several lists of records on the page, each with a different filter, and it seemed cleaner than having several slightly different recordsets. (But might be wrong!)

Anyway I tried putting the calculation at the filter stage instead and that seems to work, e.g.:

Recordset.Filter = (year(now()) & " - BirthYear > 50)"

(Just an example, don't shoot the mathematician!)

Thanks for the help!

Square