between for paramter query for text

  • Thread starter Thread starter babs
  • Start date Start date
B

babs

I know it is not the best practice to use parameter query for user input(a
form is better) but I have inhereted this database and just want to make a
few minor changes.

I have a parameter query for Client ([client] that is a text field and it
prompts for
Between [Enter beginning Client] and [Enter ending Client]

they usually just enter in a range of the alphabet
ex. L then S (thought this would include all clients beginning with L
through and including S) for some reason have to typ L then T to grab the S
-Thought that the between is Inclusive???

don't really want to add in the wildcard just want to know why the S didn't
come up - maybe BETWEEN is inclusive in numbers and only on 1st end for
text?????

thanks,
barb
 
Because 'SA' is not between 'L' and 'S'. 'SA' is greater than 'S'.

If you use between 'L' and 'SZ' then you get all the S's except for
Szyent.
 
or using :

LIKE "[L-S]*"

or, using parameters:

LIKE "[" & [enter beginning] & "-" & [enter ending] & "]*"


Vanderghast, Access MVP



KARL DEWEY said:
Because 'SA' is not between 'L' and 'S'. 'SA' is greater than 'S'.

If you use between 'L' and 'SZ' then you get all the S's except for
Szyent.

--
Build a little, test a little.


babs said:
I know it is not the best practice to use parameter query for user
input(a
form is better) but I have inhereted this database and just want to make
a
few minor changes.

I have a parameter query for Client ([client] that is a text field and it
prompts for
Between [Enter beginning Client] and [Enter ending Client]

they usually just enter in a range of the alphabet
ex. L then S (thought this would include all clients beginning with L
through and including S) for some reason have to typ L then T to grab
the S
-Thought that the between is Inclusive???

don't really want to add in the wildcard just want to know why the S
didn't
come up - maybe BETWEEN is inclusive in numbers and only on 1st end for
text?????

thanks,
barb
Click to expand...
Click to expand...
 
You can fix the problem by changing the criteria to

Between [Enter beginning Client] and [Enter ending Client] & "zzzzzzzzz"

That appends a series of z's to the last entry and therefore is highly likely
to catch all the names that start with whatever is entered in response to the
prompt.

You could even do the same with Enter Beginning Client - Just append some
spaces to the field. Then your user's could enter blank for one or both
criteria and get data based on the start or end of the alphabet.

Between [Enter beginning Client] & " " and [Enter ending Client] & "zzzzzzz"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Thanks sooo much!!! great advice

John Spencer said:
You can fix the problem by changing the criteria to

Between [Enter beginning Client] and [Enter ending Client] & "zzzzzzzzz"

That appends a series of z's to the last entry and therefore is highly likely
to catch all the names that start with whatever is entered in response to the
prompt.

You could even do the same with Enter Beginning Client - Just append some
spaces to the field. Then your user's could enter blank for one or both
criteria and get data based on the start or end of the alphabet.

Between [Enter beginning Client] & " " and [Enter ending Client] & "zzzzzzz"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL said:
Because 'SA' is not between 'L' and 'S'. 'SA' is greater than 'S'.

If you use between 'L' and 'SZ' then you get all the S's except for
Szyent.
Click to expand...
Click to expand...
 
Back
Top