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?

 

[Duane Hookom]

IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"

 

[DoveArrow]

IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP

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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.

Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"

It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.

 

[Duane Hookom]

I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP

IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP

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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.

Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"

It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.

 

[DoveArrow]

I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP

IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP
:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.

Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"

It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -

- Show quoted text -

I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."

 

[Duane Hookom]

The brackets are commonly added. Access will "help" you by adding "Like" if
you use the "*" which I don't think you want. Did you try what I suggested?

If you really think there is an "off chance that someone has the name zzzz"
then use about 50 Zs.
--
Duane Hookom
Microsoft Access MVP

I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP

On May 24, 8:49 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"

:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.

Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"

It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -

- Show quoted text -

I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."

 

[DoveArrow]

The brackets are commonly added. Access will "help" you by adding "Like" if
you use the "*" which I don't think you want. Did you try what I suggested?

If you really think there is an "off chance that someone has the name zzzz"
then use about 50 Zs.
--
Duane Hookom
Microsoft Access MVP

I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP
:
On May 24, 8:49 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP
:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.
Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"
It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -
- Show quoted text -

I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."- Hide quoted text -

- Show quoted text -

Yes. And I'm still getting dialogue boxes.

 

[DoveArrow]

The brackets are commonly added. Access will "help" you by adding "Like" if
you use the "*" which I don't think you want. Did you try what I suggested?

If you really think there is an "off chance that someone has the name zzzz"
then use about 50 Zs.

On May 25, 11:47 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP
:
On May 24, 8:49 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP
:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.
Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"
It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -
- Show quoted text -
I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."- Hide quoted text -

- Show quoted text -

Yes. And I'm still getting dialogue boxes.- Hide quoted text -

- Show quoted text -

I don't know if this helps, but here is the full sequel code for my
query. I don't understand what a lot of it means (I'm kind've a newbie
at Access), but maybe it'll help.

SELECT [New Evals].[Eval Status], [New Evals].[Person ID], [New Evals].
[Last Name], [New Evals].[First Name], [New Evals].Term, [New Evals].
[Degree Date], [New Evals].ACD, [New Evals].Location, [New Evals].
[Acad Program], [New Evals].[Acad Level], [New Evals].[UG CH transfer
type], [New Evals].[X#GRAD#STU#ACTIVE#MAJORS], [New Evals].
[X#GRAD#STU#ACTIVE#SPEC], [New Evals].[X#GRAD#STU#ACTIVE#MINORS], [New
Evals].[X#GRAD#CHAPMAN#EMAIL], [New Evals].[X#GRAD#ADVISOR#NAME], [New
Evals].[eval date], [New Evals].[eval include code], [New Evals].[eval
major status], [New Evals].Comments, [New Evals].[Added On], [New
Evals].[Added By], [New Evals].[Changed On], [New Evals].[Changed By]
FROM [New Evals] LEFT JOIN [Eval Status Table] ON [New Evals].[Person
ID] = [Eval Status Table].[Person ID]
WHERE ((([New Evals].[Last Name]) Between [Alpha Range Table]!
[AlphaStart] And [Alpha Range Table]![Alpha End] Or ([New Evals].[Last
Name]) Like [Alpha Range Table]![Alpha End] & "zzzz") AND (([New
Evals].[Acad Level])="UG") AND (([Eval Status Table].[Person ID]) Is
Null))
ORDER BY [New Evals].[Last Name];

 

[Duane Hookom]

We can't see your dialog prompts. I can only guess they are because you don't
have [Alpha Range Table] in your "FROM ...." clause so you can't reference it
in the "WHERE...." clause.

You are also using "Like " without a wildcard character. This doesn't make
sense.

Do you really have field names with 3-4 "#" symbols? You might want to find
and use a naming convention that doesn't allow spaces or symbols or
punctuation in table or field names.
--
Duane Hookom
Microsoft Access MVP

The brackets are commonly added. Access will "help" you by adding "Like" if
you use the "*" which I don't think you want. Did you try what I suggested?

If you really think there is an "off chance that someone has the name zzzz"
then use about 50 Zs.

:
On May 25, 11:47 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".

:
On May 24, 8:49 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"

:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.

Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"

It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -

- Show quoted text -

I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."- Hide quoted text -

- Show quoted text -

Yes. And I'm still getting dialogue boxes.- Hide quoted text -

- Show quoted text -

I don't know if this helps, but here is the full sequel code for my
query. I don't understand what a lot of it means (I'm kind've a newbie
at Access), but maybe it'll help.

SELECT [New Evals].[Eval Status], [New Evals].[Person ID], [New Evals].
[Last Name], [New Evals].[First Name], [New Evals].Term, [New Evals].
[Degree Date], [New Evals].ACD, [New Evals].Location, [New Evals].
[Acad Program], [New Evals].[Acad Level], [New Evals].[UG CH transfer
type], [New Evals].[X#GRAD#STU#ACTIVE#MAJORS], [New Evals].
[X#GRAD#STU#ACTIVE#SPEC], [New Evals].[X#GRAD#STU#ACTIVE#MINORS], [New
Evals].[X#GRAD#CHAPMAN#EMAIL], [New Evals].[X#GRAD#ADVISOR#NAME], [New
Evals].[eval date], [New Evals].[eval include code], [New Evals].[eval
major status], [New Evals].Comments, [New Evals].[Added On], [New
Evals].[Added By], [New Evals].[Changed On], [New Evals].[Changed By]
FROM [New Evals] LEFT JOIN [Eval Status Table] ON [New Evals].[Person
ID] = [Eval Status Table].[Person ID]
WHERE ((([New Evals].[Last Name]) Between [Alpha Range Table]!
[AlphaStart] And [Alpha Range Table]![Alpha End] Or ([New Evals].[Last
Name]) Like [Alpha Range Table]![Alpha End] & "zzzz") AND (([New
Evals].[Acad Level])="UG") AND (([Eval Status Table].[Person ID]) Is
Null))
ORDER BY [New Evals].[Last Name];

 

[DoveArrow]

We can't see your dialog prompts. I can only guess they are because you don't
have [Alpha Range Table] in your "FROM ...." clause so you can't reference it
in the "WHERE...." clause.

You are also using "Like " without a wildcard character. This doesn't make
sense.

Do you really have field names with 3-4 "#" symbols? You might want to find
and use a naming convention that doesn't allow spaces or symbols or
punctuation in table or field names.
--
Duane Hookom
Microsoft Access MVP

On May 25, 6:51 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
The brackets are commonly added. Access will "help" you by adding "Like" if
you use the "*" which I don't think you want. Did you try what I suggested?
If you really think there is an "off chance that someone has the name zzzz"
then use about 50 Zs.
--
Duane Hookom
Microsoft Access MVP
:
On May 25, 11:47 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I don't see where you entered "zzzz" like I did. It looks like you enter an
"*".
--
Duane Hookom
Microsoft Access MVP
:
On May 24, 8:49 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
IMHO parameter prompt queries are not appropriate user interface. Consider
creating a form with a couple text boxes for users to enter values. Then set
your criteria in your query to something like:
Between Forms!frmCrit!txtStart and Forms!fmrCrit!txtEnd & "zzzzzzz"
--
Duane Hookom
Microsoft Access MVP
:
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 actually what I want and was how I initially tried to build the
database. However, whenever I enter in the Criteria, Access
automatically rewrites it to look like this.
Between [Forms]![Form2]![AlphaStart] And [Forms]![Form2]![AlphaEnd]
And Like [Forms]![Form2]![AlphaEnd] & "*"
It then treats the criteria like dialogue boxes, instead of linking to
the form I want. I don't know how to make it stop doing that, so
that's why I went with dialogue boxes instead. However, if you can
provide some insight into how to make it work properly, I'd be very
much interested to hear it. Let me know.- Hide quoted text -
- Show quoted text -
I'm not sure why that should matter. The * character is a wild card
and works pretty much the way your "zzzz" statement works, except on
the off chance that someone has the name "zzzz" it'll still pull. In
any case, Access still puts brackets around my criteria, regardless of
whether I use "*" or "zzzz."- Hide quoted text -
- Show quoted text -
Yes. And I'm still getting dialogue boxes.- Hide quoted text -
- Show quoted text -

I don't know if this helps, but here is the full sequel code for my
query. I don't understand what a lot of it means (I'm kind've a newbie
at Access), but maybe it'll help.

SELECT [New Evals].[Eval Status], [New Evals].[Person ID], [New Evals].
[Last Name], [New Evals].[First Name], [New Evals].Term, [New Evals].
[Degree Date], [New Evals].ACD, [New Evals].Location, [New Evals].
[Acad Program], [New Evals].[Acad Level], [New Evals].[UG CH transfer
type], [New Evals].[X#GRAD#STU#ACTIVE#MAJORS], [New Evals].
[X#GRAD#STU#ACTIVE#SPEC], [New Evals].[X#GRAD#STU#ACTIVE#MINORS], [New
Evals].[X#GRAD#CHAPMAN#EMAIL], [New Evals].[X#GRAD#ADVISOR#NAME], [New
Evals].[eval date], [New Evals].[eval include code], [New Evals].[eval
major status], [New Evals].Comments, [New Evals].[Added On], [New
Evals].[Added By], [New Evals].[Changed On], [New Evals].[Changed By]
FROM [New Evals] LEFT JOIN [Eval Status Table] ON [New Evals].[Person
ID] = [Eval Status Table].[Person ID]
WHERE ((([New Evals].[Last Name]) Between [Alpha Range Table]!
[AlphaStart] And [Alpha Range Table]![Alpha End] Or ([New Evals].[Last
Name]) Like [Alpha Range Table]![Alpha End] & "zzzz") AND (([New
Evals].[Acad Level])="UG") AND (([Eval Status Table].[Person ID]) Is
Null))
ORDER BY [New Evals].[Last Name];- Hide quoted text -

- Show quoted text -

The # signs are from a report someone else created. I'm just linking
to it. In any case, don't worry about it. I figured out how to get my
query to do what you were originally suggesting.