Hi everybody,
I've never noticed this issue before, but it's got me baffled. I'm making a search form, and am building various SQL strings on the fly. I have to allow for comparisons whether the fields are text, numerical, or dates.
I've got most of it figured out, except I want the users to be able to enter a range of 2 values, including strings. The beginning and ending values are entered into 2 text boxes. Then the appropriate characters, (" or #) are added as necessary when building the SQL string. The numerical and date values work fine, but I'm having a problem with text values, because I'm inserting the * wildcard via code. The wildcard will go at the end (or both beginning and end), depending on the user's selection. Of course when using the "Between" choice with strings, I want the wildcard only at the END of each selection.
Here's an example: I'm filtering on the Location field.
There are 191 records that start with "A"
2 of these start with A followed by a space, i.e. "A Street..."
There are 533 records that start with "B"
340 of these start with B followed by a space, i.e. "B & B..."
If I do a filter where [Location] = "A*" it returns 191 - Correct!
If I do a filter where [Location] = "B*" it returns 533 - Correct!
Now if I do a filter where [Location] between "A*" and "B*" it returns only 529 records. The total that I want is 724, but it is missing 195 records. Strangely this total is the 2 "A" records starting with "A ", plus the 193 "B" records where it does NOT start with "B ".
I have figured out that if I append a "z" to the second value, i.e. make it [Location] between "A*" and "Bz*", it will catch all of the "B" records for a total of 722. But then it still misses the 2 "A" records starting with "A ".
This is very confusing, but if anyone has a solution, I would greatly appreciate it. If nothing else, I can just disable the "Between" choice when filtering on text fields.
Thank you very much,
huskerdon
I've never noticed this issue before, but it's got me baffled. I'm making a search form, and am building various SQL strings on the fly. I have to allow for comparisons whether the fields are text, numerical, or dates.
I've got most of it figured out, except I want the users to be able to enter a range of 2 values, including strings. The beginning and ending values are entered into 2 text boxes. Then the appropriate characters, (" or #) are added as necessary when building the SQL string. The numerical and date values work fine, but I'm having a problem with text values, because I'm inserting the * wildcard via code. The wildcard will go at the end (or both beginning and end), depending on the user's selection. Of course when using the "Between" choice with strings, I want the wildcard only at the END of each selection.
Here's an example: I'm filtering on the Location field.
There are 191 records that start with "A"
2 of these start with A followed by a space, i.e. "A Street..."
There are 533 records that start with "B"
340 of these start with B followed by a space, i.e. "B & B..."
If I do a filter where [Location] = "A*" it returns 191 - Correct!
If I do a filter where [Location] = "B*" it returns 533 - Correct!
Now if I do a filter where [Location] between "A*" and "B*" it returns only 529 records. The total that I want is 724, but it is missing 195 records. Strangely this total is the 2 "A" records starting with "A ", plus the 193 "B" records where it does NOT start with "B ".
I have figured out that if I append a "z" to the second value, i.e. make it [Location] between "A*" and "Bz*", it will catch all of the "B" records for a total of 722. But then it still misses the 2 "A" records starting with "A ".
This is very confusing, but if anyone has a solution, I would greatly appreciate it. If nothing else, I can just disable the "Between" choice when filtering on text fields.
Thank you very much,
huskerdon
