between for paramter query for text

[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

 

[KARL DEWEY]

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.

 

[vanderghast]

or using :

LIKE "[L-S]*"

or, using parameters:

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


Vanderghast, Access MVP

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.

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

 

[John Spencer]

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

 

[babs]

Thanks sooo much!!! great advice

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

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.