Ranges in Criteria of a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
 
Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

Tom Ellison said:
Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
 
Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

Tom Ellison said:
Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

Tom Ellison said:
Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

Tom Ellison said:
Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
Dear Dawn:

The answer depends on what affect you need. If you need all the rows
up to and including R0, then add that. What if there is an S7? Do
you mean to exclude that one? Is it including rows you don't want if
you use ZZZZ?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

Tom Ellison said:
Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

:

Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
I believe we would then include the ones that are C0 (which we don't want).
Those are the only two choices. Can I do what you said except in the quotes
at the end put "R0"? There would never be another set of letters needed (at
least for this query.)

Tom Ellison said:
Dear Dawn:

The answer depends on what affect you need. If you need all the rows
up to and including R0, then add that. What if there is an S7? Do
you mean to exclude that one? Is it including rows you don't want if
you use ZZZZ?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

Tom Ellison said:
Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 11:29:02 -0800, "Dawn"

The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

:

Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
Dear Dawn:

Well, hopefully I'm catching on to what you want.

It seems then that what you have is that the column for you Subgroups
is actually two separate values. The first 5 characters are separate
from the last two values. I'll mention breifly that it is an error to
design the data this way.

So, what you need may be to say:

WHERE LEFT(Subgroup, 5) BETWEEN [Subgroup start] AND [Subgroup end]
AND RIGHT(Subgroup, 2) = "R0"

of course, this is only a portion of the criteria you need, but it is
the portion we're discussing.

Now, is this logically what you intend?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I believe we would then include the ones that are C0 (which we don't want).
Those are the only two choices. Can I do what you said except in the quotes
at the end put "R0"? There would never be another set of letters needed (at
least for this query.)

Tom Ellison said:
Dear Dawn:

The answer depends on what affect you need. If you need all the rows
up to and including R0, then add that. What if there is an S7? Do
you mean to exclude that one? Is it including rows you don't want if
you use ZZZZ?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

:

Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 11:29:02 -0800, "Dawn"

The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

:

Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
Actually, I think I'm completely confused about what you are talking about.
However, I used the &"zzzz" thing you mentioned before and changed it to be
&"zzzzzR0" and now it works just great. So, I have fixed my problem, but if
you would like to respond with another explanation of your last post, it
might be a good thing for me to understand. It's up to you. Thank you so
much for your patient help!!

Tom Ellison said:
Dear Dawn:

Well, hopefully I'm catching on to what you want.

It seems then that what you have is that the column for you Subgroups
is actually two separate values. The first 5 characters are separate
from the last two values. I'll mention breifly that it is an error to
design the data this way.

So, what you need may be to say:

WHERE LEFT(Subgroup, 5) BETWEEN [Subgroup start] AND [Subgroup end]
AND RIGHT(Subgroup, 2) = "R0"

of course, this is only a portion of the criteria you need, but it is
the portion we're discussing.

Now, is this logically what you intend?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I believe we would then include the ones that are C0 (which we don't want).
Those are the only two choices. Can I do what you said except in the quotes
at the end put "R0"? There would never be another set of letters needed (at
least for this query.)

Tom Ellison said:
Dear Dawn:

The answer depends on what affect you need. If you need all the rows
up to and including R0, then add that. What if there is an S7? Do
you mean to exclude that one? Is it including rows you don't want if
you use ZZZZ?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 12:19:01 -0800, "Dawn"

I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

:

Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 11:29:02 -0800, "Dawn"

The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

:

Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
Dear Dawn:

I cannot begin to see how you have solved this the way you say.
Apparently I have less understanding of the situation than I thought.
If you got the help you need, then it was apparently largely by
accident. But I'm glad it helped.

I think I'd prefer to accept your thanks and let it go for the time
being, unless you have some further problem with it.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Actually, I think I'm completely confused about what you are talking about.
However, I used the &"zzzz" thing you mentioned before and changed it to be
&"zzzzzR0" and now it works just great. So, I have fixed my problem, but if
you would like to respond with another explanation of your last post, it
might be a good thing for me to understand. It's up to you. Thank you so
much for your patient help!!

Tom Ellison said:
Dear Dawn:

Well, hopefully I'm catching on to what you want.

It seems then that what you have is that the column for you Subgroups
is actually two separate values. The first 5 characters are separate
from the last two values. I'll mention breifly that it is an error to
design the data this way.

So, what you need may be to say:

WHERE LEFT(Subgroup, 5) BETWEEN [Subgroup start] AND [Subgroup end]
AND RIGHT(Subgroup, 2) = "R0"

of course, this is only a portion of the criteria you need, but it is
the portion we're discussing.

Now, is this logically what you intend?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


I believe we would then include the ones that are C0 (which we don't want).
Those are the only two choices. Can I do what you said except in the quotes
at the end put "R0"? There would never be another set of letters needed (at
least for this query.)

:

Dear Dawn:

The answer depends on what affect you need. If you need all the rows
up to and including R0, then add that. What if there is an S7? Do
you mean to exclude that one? Is it including rows you don't want if
you use ZZZZ?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 12:19:01 -0800, "Dawn"

I think so. It seems like maybe I should set up a way for it to add the R0
on the end or something since I don't have all numbers or letters. Is there
a way for the field to fill in the R0 at the end when the user enters the
first 5 characters?

:

Dear Dawn:

Oh, I thought these were dates. Interestingly, it is somewhat
similar, however.

If you filter to values that are, in effect, less than or equal to
W1801, then you won't get W1801R0. That's simply because W1801R0 is
greater than W1801.

Now, if the characters are all letters or digits, you could have the
query search:

BETWEEN [Begin Here] AND [End Here] & "ZZZZ"

I used 4 Zs, but you might need more. Does this help?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 11:29:02 -0800, "Dawn"

The field that is having the problem uses a format of W1801. The letter and
numbers can change, but it's always one letter and 4 numbers. It is looking
in a specific field which actually contains information in the format of
W1801R0, but the "R0" on the end is always the same so we have been just
entering the first 5 characters. Does that help you understand what might be
my problem?

:

Dear Dawn:

A common cause of confusion that may apply to your situation is that,
in the data, the Date/Time values may contain Time components that are
not zero.

The BETWEEN operator includes all rows that are less than or equal to
the ending value. That's the behavior you're seeing in the first
case.

In the second case, if the criteria has and ending date of 11/15/04 it
will include rows up to 11/15/04 at 0:00:00 but this will exclude rows
that are 11/15/04 0:00:01 and later - that is, most of that day.

The next thing to do, I would suggest, is to see if this is the case.
We can discuss solutions later, OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Mon, 22 Nov 2004 10:11:02 -0800, "Dawn"

I have two different items which use Between...and statements and ask the
user for beginning and ending information. For one of them the system is
grabbing the ending information and including it in the query. For the
other, it is excluding the ending information. I have looked at both queries
and they look the same to me. Here they are...
Between [Frame Seq Start] And [Frame Seq End]
Between [Subgroup start] And [Subgroup end]
Is there some rule about whether the ending info is included in the query
that I don't know about?
Thanks for any information.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top