Re: Help with select/between

In article: <43uhcu$4k7@ftcnews.nrcs.usda.gov> tammy@scooter (Tammy Croteau) writes: > > In a table I have the following data: > AGCR > AGST > ALAR > ANGE > AVSA > AXXA > BRAM > > The user wants to wildcard select to get different variations of > data. If they enter in A*-AV* they expect to get the first 5 items > on the list. > > My sql statement is as follows: > select <field_name> from <table> where <field_name> between "A*" and "AV*" > > The problem is that all data up to (but not including) AV* is returned. The > only way I have found to get the AV* data is to include a matches AV* clause > on the end of the statement. > > Is there a better way?????? Any help is much appreciated! At the risk of sounding facetious (which I apparently frequently do!), I would advise a little user education: try getting them to search for AA*-AV*. You can kludge the code by saying IF str[1,2] = "A*" THEN LET str = "A", str CLIPPED .. The problem you have is that if they ever want all A's and enter A*, you will then force in the extra A, and they'll only get AA*. You can code around this too, if you want, but it'll be a real handful catering for all the special cases. HTH, Spokey the Wheeler------------------------------------------------------------- "Billy Boy" - das aufregend andere kondom! Carter - the Unstoppable Sex Machine / Wheeler - the Unstoppable Mail Machine!