Rick,

I have interspersed my answers to your questions/comments. . .
>Hi,
>
>Thanks all for your suggestions, I am learning heaps if nothing else. To many Auzy programmers have to big an ego to share their knowledge. I thank you for being open with yours....
>
>Now that I have sucked up ha ha, can I ask you this.
>
>How do you create an index with a filter on a BETWEEN() function?
>Would this not be hard coded, e.g how could I replace the range options with dynamic values.
>I do have an index on every field so i do not understand what you mean by saying you doubt if i have indices on my BETWEEN and INLIST option.

RIght - I don't know how to create an index on a "dynamic" BETWEEN either. That's why I simply pointed out that you say you have indexes on everything, but you don't really.
>
>The fiedls used are TAGs etc.. on there own. I have not used Functions to TRIM the indices, should I?
>

This is the main problem (if I read you correctly)! *IF* you are going to use a funxction in your WHERE clause, then that EXACT SAME function *must* be coded in the index too FOR RUSHMORE TO BE EFFECTIVE. In other words, as I tried to say before, your index expression must look identical to your WHERE clause in terms of structure.

>I am using SET KEYS. e.g. SET ORDER TO IDXDefault where
>IDXDefault = INDEX ON Suburb+street+regions TAG IDXDefault, then
>SET KEY TO Var1+Var2+Var3 where
>Var1=Suburb
>Var2=Street
>Var3=Regions for example.
>
>This is quick on a single table, I'm not sure how this would go on multiple JOINED tables. It's also no good for a SELECT statement. Because SELE don't honour the filter from the key espression.
>
>Can you give me an example of how you would create indices for the above criteria and how you would use them in a select taking advantage of Rushmore.
>
The index above would be used with a Where clause along the lines of:
. . . Where a.suburb+a.street+a.regions == m.suburb+m.street+m.regions. . .
- That's what is meant by having the expressions *MATCH* the index expression.

>Oh Yeah! I looked up help for between(), found nothing interesting. It just gives you syntax help only. the tastraders and solutions are a little underdone for my liking too.
>
What I said was that SQL has a different format for the BETWEEN clause, which is only documented in the Examples under Select-SQL. Look there to find it and copy its format in your code.
>Look forward to your reply.
>TIA
>Rick
The most fundamental thing that I could recommend to you is to read CAREFULLY - both posts and Documentation. From your questions you did *NOT* seem to catch what I wrote the first time (and I think you should have).

Good luck,

Jim N