Last Name Between...And Statements

[DoveArrow]

Hopefully someone can help me with this. I'm trying to create an
Access database that will pull a list of people based on the first two
letters of their last name. Since this list will vary, depending on
the user of the database, I want to use dialogue boxes so that a
person can put in their own parameters for the list. For example, say
my list is Dn-Km. A dialogue box will pop up, asking for the beginning
of the alpha range. Then, once the person clicks on the OK button, a
second dialogue box will pop up, asking for the end of the alpha
range.

So far, I've figured out that you can't use a Between...And statement
in quite the manner that I've explained. For example, if you put in Dn
as the beginning of the alpha range and Km as the end of the alpha
range, it won't pull, for example, someone's last name that begins
with Kma. That wasn't too probelmatic to overcome, I just input the
beginning of the next alpha range, Kn, and wrote the Criteria line
this way.

Between [Enter Beginning of Alpha Range] And [Enter End of Alpha
Range] And <>[Enter End of Alpha Range]

So that worked great. The problem is, what happens if I have a range
that's Wa-Zz? In this case, anyone's last name that begins with Zz
will not be included in my data pull. Sure, it's unlikely that
someone's last name will begin with Zz, but it's not unheard of. So
what I'm wondering is, how do I get my database to pull a last name
that begins with Zz, without causing the rest of my people to pull
names not included in their alpha range? Thoughts?

 

[Guest]

Try this, substituting your table and field names ---
WHERE (((EMP.LastName) Between [Enter Beginning of Alpha Range] & "*" And
[Enter end of Alpha Range] & "*")) OR (((EMP.LastName) Like [Enter end of
Alpha Range] & "*"))

 

[DoveArrow]

Try this, substituting your table and field names ---
WHERE (((EMP.LastName) Between [Enter Beginning of Alpha Range] & "*" And
[Enter end of Alpha Range] & "*")) OR (((EMP.LastName) Like [Enter end of
Alpha Range] & "*"))
--
KARL DEWEY
Build a little - Test a little

Hopefully someone can help me with this. I'm trying to create an
Access database that will pull a list of people based on the first two
letters of their last name. Since this list will vary, depending on
the user of the database, I want to use dialogue boxes so that a
person can put in their own parameters for the list. For example, say
my list is Dn-Km. A dialogue box will pop up, asking for the beginning
of the alpha range. Then, once the person clicks on the OK button, a
second dialogue box will pop up, asking for the end of the alpha
range.

So far, I've figured out that you can't use a Between...And statement
in quite the manner that I've explained. For example, if you put in Dn
as the beginning of the alpha range and Km as the end of the alpha
range, it won't pull, for example, someone's last name that begins
with Kma. That wasn't too probelmatic to overcome, I just input the
beginning of the next alpha range, Kn, and wrote the Criteria line
this way.

Between [Enter Beginning of Alpha Range] And [Enter End of Alpha
Range] And <>[Enter End of Alpha Range]

So that worked great. The problem is, what happens if I have a range
that's Wa-Zz? In this case, anyone's last name that begins with Zz
will not be included in my data pull. Sure, it's unlikely that
someone's last name will begin with Zz, but it's not unheard of. So
what I'm wondering is, how do I get my database to pull a last name
that begins with Zz, without causing the rest of my people to pull
names not included in their alpha range? Thoughts?- Hide quoted text -

- Show quoted text -

That's BRILLIANT! Thank you.