filter with NOT IN

[Boxhead]

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

[glenngv]

Put the letters in an array then loop through it to form the filter string.

[Boxhead]

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

[whammy]

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?

[Boxhead]

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

[[email protected]]

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:

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!

[Boxhead]

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

[glenngv]

[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')

[Boxhead]

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

[[email protected]]

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! )

[Boxhead]

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

[[email protected]]

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)

<plug type="gratuitous">Are any of you following my [url=http://marcustucker.com/blog/]blog[/url]?</plug>

[Boxhead]

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

[[email protected]]

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...