Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 13 of 13
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts

    how I get a recordset that contains only records with Price > $90.00 ?

    ado/sql
    how to program
    a table has ProductName and Price fields
    how I get a recordset that contains only records with Price > $90.00 ?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    the x = "SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'" may included in oRS.Open x, oConn ....is correct with WHERE ? how to sort same time by name or price ?

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    Code:
    oRS.Open "SELECT ProductName,Price FROM yourTable WHERE Price > '90.00'", conn
    Yes, you can use oRS.Open with WHERE statements. You can do any valid SELECT SQL code with oRS.Open.


    TO ORDER BY Price (use ASC for Ascending order, use DESC for Descending order, removing the | character)

    ...WHERE Price > '90.00' ORDER BY Price ASC|DESC

    TO ORDER BY ProductName (use ASC for Ascending order, use DESC for Descending order, removing the | character)

    ...WHERE Price > '90.00' ORDER BY ProductName ASC|DESC
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #5
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ANOTHER FILTER
    THIS can be done ?

    strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"
    oRS.Filter = strFilter

  • #6
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    how to use "....ORDER BY Price ASC|DESC..." with the other way using

    oRS.Sort = strSorting
    oRS.Filter = strFilter

    strSorting=?
    strFilter=?

  • #7
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    You can use the ORDER BY with the filter, as the 2 are unrelated. However, the .Sort method won't help you, as the sorting should be done on the SQL server.

    However, you should not use the ADO .Filter, as this can easily be accomplished with SQL. Putting this into the ADO .Filter method is more work on the Web server which should only be handing out documents, not parsing SQL.
    Last edited by Daemonspyre; 07-11-2007 at 01:09 PM.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #8
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ANOTHER FILTER
    THIS can be done ?

    strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"
    oRS.Filter = strFilter

  • #9
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Quote Originally Posted by Daemonspyre View Post
    SELECT ProductName, Price FROM yourTable WHERE Price > '90.00'
    The price value should have no quotes.
    Code:
    SELECT ProductName, Price FROM yourTable WHERE Price > 90.00 order by Price
    If 90.00 is in a variable.
    Code:
    sql = "SELECT ProductName, Price FROM yourTable WHERE Price > " & amount & "  order by Price"

  • #10
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    what about compare two fields together, can be done ?
    eg.
    strFilter = "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'"

  • #11
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Try:
    Code:
    strFilter = "UnitsInStok < ReorderLevel"
    oRS.Filter = strFilter
    Links on using Filter.
    http://www.asp101.com/tips/index.asp?id=86
    http://www.devguru.com/technologies/...et_filter.html

  • #12
    Regular Coder
    Join Date
    Dec 2005
    Posts
    702
    Thanks
    0
    Thanked 0 Times in 0 Posts
    "UnitsInStok < ReorderLevel" these are from the table record ? They do not need oRS like "'" oRS("UnitsInStok") "'<'" oRS("ReorderLevel") "'" ?

  • #13
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,043
    Thanks
    0
    Thanked 251 Times in 247 Posts
    Yes. Did you try it? Filters are just exactly like the conditions you put in the WHERE clause. Go read the links I posted in my previous post.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •