Web Analytics Made Easy -
StatCounter filter with NOT IN - CodingForum

Announcement

Collapse
No announcement yet.

filter with NOT IN

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • filter with NOT IN

    I am using filter to create a contact card system. The database holds all records and then a filter gets the records for the relevant tab ie ABC -

    FilterParam = Recordset_all.Fields.Item("lnk_name").Value
    Recordset_all.Filter = "lnk_name LIKE 'A*' OR lnk_name LIKE 'B*' OR lnk_name LIKE 'C*'"

    For my last card I want to show all records that dont start with a letter. In SQL I would put -

    "SELECT * FROM t_links WHERE LEFT(lnk_name,1)NOT IN ('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y' ,'Z')"

    How can I do the same in vbscript - I dont want to have to put the 'lnk_name LIKE' code 26 times!!

    Monkey
    Monkey

    My head hurts!

  • #2
    Put the letters in an array then loop through it to form the filter string.
    Glenn
    vBulletin Mods That Rock!

    Comment


    • #3
      At the moment, I have the 'other' records as a seperate recordset using the 'NOT IN' SQL as shown. Which would be the quicker to load, this way or the array that you suggest?

      How can I learn about the speed of running code such as recordsets and arrays?

      monkey
      Monkey

      My head hurts!

      Comment


      • #4
        I use NOT IN() all the time with checkboxes and arrays. But I'm really not clear on what you're trying to do.

        If you're posting a checkbox to a form, then it's already set up for you really... can you give an example "stripped down" form?
        Former ASP Forum Moderator - I'm back!

        If you can teach yourself how to learn, you can learn anything. ;)

        Comment


        • #5
          whammy

          Not a form at all! Go to www.nhw-wilts.org.uk/contact_info.asp

          Each 'card' is taken from a db containing all contacts, filtered by the first letter ie abc, def etc. For the final card 'other' I need to filter by NOT a,b,c,d,e...... At the moment, I have just created another recordset and used the sql NOT IN, as I would have to use OR lnk_name LIKE 26 times rather than just delimiting in SQL!

          glenngv suggested an array to loop through the alphabet, but I dont know if this would be any quicker for a server to serve than just creating a second RS?

          Steve
          Monkey

          My head hurts!

          Comment


          • #6
            I suggest modifying the query which is executed to populate the recordset - add a new field which is the uppercase leftmost character (i.e. "UPPER(LEFT(lnk_name, 1)) AS FirstLetter").

            Then you can filter the recordset on each first letter easily as you loop from A-Z, and finally catch all the non-alphabetic first characters (for your "other" card) by using a .Filter that looks something like:

            Code:
            RS.Filter = "(FirstLetter <> 'A') AND (FirstLetter <> 'B') AND (FirstLetter <> 'C') AND... etc. etc.
            You could hardcode this filter string dynamically, or build it as you loop through the letters, or even fill a 26-element 1D array with each letter (as you loop through), then build the filter string with a cunning call to a Join() statement!

            This latter method is the most efficient way I can think of, since it avoids classic string concatenation performance problems, but for such a relatively small data set it will probably make no measurable difference. It's definitely the neatest solution of the three though!
            Marcus Tucker / www / blog
            Web Analyst Programmer / Voted SPF "ASP Guru"

            Comment


            • #7
              I think I will leave it as is. The work needed:improved speed ratio is too great for me!!

              What do you think of it anyway? I'm quite proud of it myself - not a layer in sight, recordset repeat region down not across and funky little tabs! (simple things!)

              Monkey
              Monkey

              My head hurts!

              Comment


              • #8
                [email protected]'s suggestion in action: (without looping)

                dim arr, filterStr
                arr = Array("A","B","C","D","E") 'etc...
                filterStr = join(arr,"') AND (FirstLetter <> '")
                response.write "(FirstLetter <> '" & filterStr & "')"

                Output:

                (FirstLetter <> 'A') AND (FirstLetter <> 'B') AND (FirstLetter <> 'C') AND (FirstLetter <> 'D') AND (FirstLetter <> 'E')
                Glenn
                vBulletin Mods That Rock!

                Comment


                • #9
                  glenngv, [email protected]

                  a lot simpler than I thought! I now have one recordset, a new FirstLetter field and the join function!! Nice and tidy!

                  How does the join work? Does it loop through an array on its own?

                  Cheers Both

                  monkey
                  Monkey

                  My head hurts!

                  Comment


                  • #10
                    Join() is the opposite of Split(), and this pair of functions allows you to convert between delimited strings and 1D arrays. Read up in your VBScript help file for the full lowdown and examples.

                    And yes, I like the page - nice clean use of client-side layer switching (I did something very similar at http://www.ashdown-it.co.uk/ many years ago... they said it couldn't be done... a red rag to a bull! )
                    Marcus Tucker / www / blog
                    Web Analyst Programmer / Voted SPF "ASP Guru"

                    Comment


                    • #11
                      Thanks! Not a layer in site though - its all done with colapsed table cells - made for some interesting coding I thought and no problems with layer positions, netscape (I dont think, havent tested it!) etc

                      Monkey
                      Monkey

                      My head hurts!

                      Comment


                      • #12
                        Aha! Didn't actually look at the code - was stealing a few minutes to make a post while at lunch here in Italy...

                        Not sure about the backwards compatibility of table cell collapsing, and can't check it from here, but as long as all the content is accessible in older browsers (i.e. visible and clickable, even though it's probably not pretty), and it works in all the modern browsers that you intend it to, then it's all cool! (The Ashdown site DOES work in NS4, incidentally)

                        Code:
                        <plug type="gratuitous">Are any of you following my [url=http://marcustucker.com/blog/]blog[/url]?</plug>
                        Marcus Tucker / www / blog
                        Web Analyst Programmer / Voted SPF "ASP Guru"

                        Comment


                        • #13
                          <plug type="jealous">I'm at work you lucky *****!!</plug>
                          Monkey

                          My head hurts!

                          Comment


                          • #14
                            Originally posted by Boxhead
                            <plug type="jealous">I'm at work you lucky *****!!</plug>
                            So am I! I feel your pain. It's a tough life...
                            Marcus Tucker / www / blog
                            Web Analyst Programmer / Voted SPF "ASP Guru"

                            Comment

                            Working...
                            X