Access 2000 Query using "Between Operator"

  • Thread starter Thread starter Donald Regener
  • Start date Start date
D

Donald Regener

I am developing a Query that requires a "Between Operator"

The field requiring the Operator started as a "Text
Field". The field was converted to a Number Field by use
of the Val() Function. Unfortunately the Data can be a
3, 4, or 5 character field (In the Native Table). I am
trying to Use the "Between Operator" with Prompts. The
Query works as desired with a "Fixed Value" (Number
Embedded in Between Statment" vice entered as part of
the "Prompt Response").

The problem is associated with the 3 and 4 character
data. IF the 3 and 4 character data were excluded
(>9999), the query would work as desired. When the 3 and
4 character data is included, the query results looks
like a "Text Search" on the first characters. The 3 and
4 Character Data is intermingled with the Correct 5 Digit
Data. (Example Between 10,000 and 10999 entered from
Prompts would return: 10130, 1024, 113, 11405, etc). I
have tried enclosing the Prompt field with a Val()
Function (From SQL View) and enclosing with the Trim()
Function. Neither has provided the desired results.

I cannot change the 3 & 4 Character data to 5
Characters. I lost that request this morning. I do not
know how to fix it, but it appears the Prompt field has
some characters associate with it beside the ones typed
as the response to the Prompt.

All help Gladly accepted.
 
If you work with whole (integral) numbers, try:

WHERE CLng([YourField]) BETWEEN LowerLimit AND UpperLimit

If it still doesn't work, try:

WHERE CLng([YourField]
BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)
 
Van:

First, I would like to thank you for your response.

Second, Let me try and clarify the requirement to see IF
you have answered my question ....

(1) The "Range" Lower and Upper Limit is in response to
a "Prompt" (Example: Between [LowerLimit] AND
[UpperLimit] ... where does CLong go in this Code ... Edit
SQL View ???
(2) The Actual Values of the field I am working with, is
the Result of a "Val() Function). It starts as a "Text
Field" and is run thru the Val() Function.

Is you response based upon these requirements/conditions???

Again, I appreciate your help.
-----Original Message-----
If you work with whole (integral) numbers, try:

WHERE CLng([YourField]) BETWEEN LowerLimit AND UpperLimit

If it still doesn't work, try:

WHERE CLng([YourField]
BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)

--
HTH
Van T. Dinh
MVP (Access)



I am developing a Query that requires a "Between Operator"

The field requiring the Operator started as a "Text
Field". The field was converted to a Number Field by use
of the Val() Function. Unfortunately the Data can be a
3, 4, or 5 character field (In the Native Table). I am
trying to Use the "Between Operator" with Prompts. The
Query works as desired with a "Fixed Value" (Number
Embedded in Between Statment" vice entered as part of
the "Prompt Response").

The problem is associated with the 3 and 4 character
data. IF the 3 and 4 character data were excluded
(>9999), the query would work as desired. When the 3 and
4 character data is included, the query results looks
like a "Text Search" on the first characters. The 3 and
4 Character Data is intermingled with the Correct 5 Digit
Data. (Example Between 10,000 and 10999 entered from
Prompts would return: 10130, 1024, 113, 11405, etc). I
have tried enclosing the Prompt field with a Val()
Function (From SQL View) and enclosing with the Trim()
Function. Neither has provided the desired results.

I cannot change the 3 & 4 Character data to 5
Characters. I lost that request this morning. I do not
know how to fix it, but it appears the Prompt field has
some characters associate with it beside the ones typed
as the response to the Prompt.

All help Gladly accepted.
Click to expand...


.
Click to expand...
 
Post the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)


Donald Regener said:
Van:

First, I would like to thank you for your response.

Second, Let me try and clarify the requirement to see IF
you have answered my question ....

(1) The "Range" Lower and Upper Limit is in response to
a "Prompt" (Example: Between [LowerLimit] AND
[UpperLimit] ... where does CLong go in this Code ... Edit
SQL View ???
(2) The Actual Values of the field I am working with, is
the Result of a "Val() Function). It starts as a "Text
Field" and is run thru the Val() Function.

Is you response based upon these requirements/conditions???

Again, I appreciate your help.
-----Original Message-----
If you work with whole (integral) numbers, try:

WHERE CLng([YourField]) BETWEEN LowerLimit AND UpperLimit

If it still doesn't work, try:

WHERE CLng([YourField]
BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)

--
HTH
Van T. Dinh
MVP (Access)



I am developing a Query that requires a "Between Operator"

The field requiring the Operator started as a "Text
Field". The field was converted to a Number Field by use
of the Val() Function. Unfortunately the Data can be a
3, 4, or 5 character field (In the Native Table). I am
trying to Use the "Between Operator" with Prompts. The
Query works as desired with a "Fixed Value" (Number
Embedded in Between Statment" vice entered as part of
the "Prompt Response").

The problem is associated with the 3 and 4 character
data. IF the 3 and 4 character data were excluded
(>9999), the query would work as desired. When the 3 and
4 character data is included, the query results looks
like a "Text Search" on the first characters. The 3 and
4 Character Data is intermingled with the Correct 5 Digit
Data. (Example Between 10,000 and 10999 entered from
Prompts would return: 10130, 1024, 113, 11405, etc). I
have tried enclosing the Prompt field with a Val()
Function (From SQL View) and enclosing with the Trim()
Function. Neither has provided the desired results.

I cannot change the 3 & 4 Character data to 5
Characters. I lost that request this morning. I do not
know how to fix it, but it appears the Prompt field has
some characters associate with it beside the ones typed
as the response to the Prompt.

All help Gladly accepted.
Click to expand...


.
Click to expand...
Click to expand...
 
Van:

The Second Version Works as desired.

Code posted below.

Appreciate your help.

Don

Query1: 2 Contract: Booth Notification Report
Query_byBooth#
SELECT [2 Contract].Booth_Number, [0408wsa1].booth, Val
([Booth]) AS
Sort, [2 Contract].Contract_Number, [ContactFN] & " " &
[ContactLN]
AS Contact, [2 Contract].Company, [2 Contract].Address, [2
Contract].Address2, [2 Contract].City, [2 Contract].State,
[2
Contract].Zip, [2 Contract].Country, [2 Contract].Phone,
[2
Contract].Fax, [2 Contract].Email, [2 Contract].Venue,
[BoothSizeX]
& " X " & [BoothSizeY] AS BoothSize, [2 Contract].S01, [2
Contract].S02, [2 Contract].S03, [2 Contract].S04
FROM [2 Contract] INNER JOIN 0408wsa1 ON [2
Contract].Cust_ID =
[0408wsa1].cust_id
WHERE ((Not ([0408wsa1].booth) Is Null) AND (([2
Contract].Venue)
Not Like "Collections"));


Query2: 2 Contract: Booth Notification Report
Query_byBooth#2
SELECT [2 Contract: Booth Notification Report
Query_byBooth#].Booth_Number, [2 Contract: Booth
Notification Report
Query_byBooth#].booth, [2 Contract: Booth Notification
Report
Query_byBooth#].Sort, [2 Contract: Booth Notification
Report
Query_byBooth#].Contract_Number, [2 Contract: Booth
Notification
Report Query_byBooth#].Contact, [2 Contract: Booth
Notification
Report Query_byBooth#].Company, [2 Contract: Booth
Notification
Report Query_byBooth#].Address, [2 Contract: Booth
Notification
Report Query_byBooth#].Address2, [2 Contract: Booth
Notification
Report Query_byBooth#].City, [2 Contract: Booth
Notification Report
Query_byBooth#].State, [2 Contract: Booth Notification
Report
Query_byBooth#].Zip, [2 Contract: Booth Notification
Report
Query_byBooth#].Country, [2 Contract: Booth Notification
Report
Query_byBooth#].Phone, [2 Contract: Booth Notification
Report
Query_byBooth#].Fax, [2 Contract: Booth Notification
Report
Query_byBooth#].Email, [2 Contract: Booth Notification
Report
Query_byBooth#].Venue, [2 Contract: Booth Notification
Report
Query_byBooth#].BoothSize, [2 Contract: Booth Notification
Report
Query_byBooth#].S01, [2 Contract: Booth Notification
Report
Query_byBooth#].S03, [2 Contract: Booth Notification
Report
Query_byBooth#].S04
FROM [2 Contract: Booth Notification Report Query_byBooth#]
WHERE (((CLng([2 Contract: Booth Notification Report
Query_byBooth#].[Sort])) Between CLng([First Booth#]) And
CLng([Last
Booth#])))
ORDER BY [2 Contract: Booth Notification Report
Query_byBooth#].Sort;
-----Original Message-----
Post the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)


Van:

First, I would like to thank you for your response.

Second, Let me try and clarify the requirement to see IF
you have answered my question ....

(1) The "Range" Lower and Upper Limit is in response to
a "Prompt" (Example: Between [LowerLimit] AND
[UpperLimit] ... where does CLong go in this Code ... Edit
SQL View ???
(2) The Actual Values of the field I am working with, is
the Result of a "Val() Function). It starts as a "Text
Field" and is run thru the Val() Function.

Is you response based upon these requirements/conditions???

Again, I appreciate your help.
-----Original Message-----
If you work with whole (integral) numbers, try:

WHERE CLng([YourField]) BETWEEN LowerLimit AND UpperLimit

If it still doesn't work, try:

WHERE CLng([YourField]
BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)

--
HTH
Van T. Dinh
MVP (Access)



I am developing a Query that requires a "Between Operator"

The field requiring the Operator started as a "Text
Field". The field was converted to a Number Field by use
of the Val() Function. Unfortunately the Data can be a
3, 4, or 5 character field (In the Native Table). I am
trying to Use the "Between Operator" with Prompts. The
Query works as desired with a "Fixed Value" (Number
Embedded in Between Statment" vice entered as part of
the "Prompt Response").

The problem is associated with the 3 and 4 character
data. IF the 3 and 4 character data were excluded
(>9999), the query would work as desired. When the 3 and
4 character data is included, the query results looks
like a "Text Search" on the first characters. The 3 and
4 Character Data is intermingled with the Correct 5 Digit
Data. (Example Between 10,000 and 10999 entered from
Prompts would return: 10130, 1024, 113, 11405, etc). I
have tried enclosing the Prompt field with a Val()
Function (From SQL View) and enclosing with the Trim ()
Function. Neither has provided the desired results.

I cannot change the 3 & 4 Character data to 5
Characters. I lost that request this morning. I do not
know how to fix it, but it appears the Prompt field has
some characters associate with it beside the ones typed
as the response to the Prompt.

All help Gladly accepted.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Perhaps you should try to follow the naming convention to make it easier to
read and decode. Also, don't use spaces / special characters in names.
They simply add complications when you do coding.

See The Access Web:

http://www.mvps.org/access/general/gen0012.htm

Most Access books (at least the reasonable ones) also have info. on the
Leszynski/Reddick naming conventions.

--
HTH
Van T. Dinh
MVP (Access)


Donald Regener said:
Van:

The Second Version Works as desired.

Code posted below.

Appreciate your help.

Don

Query1: 2 Contract: Booth Notification Report
Query_byBooth#
SELECT [2 Contract].Booth_Number, [0408wsa1].booth, Val
([Booth]) AS
Sort, [2 Contract].Contract_Number, [ContactFN] & " " &
[ContactLN]
AS Contact, [2 Contract].Company, [2 Contract].Address, [2
Contract].Address2, [2 Contract].City, [2 Contract].State,
[2
Contract].Zip, [2 Contract].Country, [2 Contract].Phone,
[2
Contract].Fax, [2 Contract].Email, [2 Contract].Venue,
[BoothSizeX]
& " X " & [BoothSizeY] AS BoothSize, [2 Contract].S01, [2
Contract].S02, [2 Contract].S03, [2 Contract].S04
FROM [2 Contract] INNER JOIN 0408wsa1 ON [2
Contract].Cust_ID =
[0408wsa1].cust_id
WHERE ((Not ([0408wsa1].booth) Is Null) AND (([2
Contract].Venue)
Not Like "Collections"));


Query2: 2 Contract: Booth Notification Report
Query_byBooth#2
SELECT [2 Contract: Booth Notification Report
Query_byBooth#].Booth_Number, [2 Contract: Booth
Notification Report
Query_byBooth#].booth, [2 Contract: Booth Notification
Report
Query_byBooth#].Sort, [2 Contract: Booth Notification
Report
Query_byBooth#].Contract_Number, [2 Contract: Booth
Notification
Report Query_byBooth#].Contact, [2 Contract: Booth
Notification
Report Query_byBooth#].Company, [2 Contract: Booth
Notification
Report Query_byBooth#].Address, [2 Contract: Booth
Notification
Report Query_byBooth#].Address2, [2 Contract: Booth
Notification
Report Query_byBooth#].City, [2 Contract: Booth
Notification Report
Query_byBooth#].State, [2 Contract: Booth Notification
Report
Query_byBooth#].Zip, [2 Contract: Booth Notification
Report
Query_byBooth#].Country, [2 Contract: Booth Notification
Report
Query_byBooth#].Phone, [2 Contract: Booth Notification
Report
Query_byBooth#].Fax, [2 Contract: Booth Notification
Report
Query_byBooth#].Email, [2 Contract: Booth Notification
Report
Query_byBooth#].Venue, [2 Contract: Booth Notification
Report
Query_byBooth#].BoothSize, [2 Contract: Booth Notification
Report
Query_byBooth#].S01, [2 Contract: Booth Notification
Report
Query_byBooth#].S03, [2 Contract: Booth Notification
Report
Query_byBooth#].S04
FROM [2 Contract: Booth Notification Report Query_byBooth#]
WHERE (((CLng([2 Contract: Booth Notification Report
Query_byBooth#].[Sort])) Between CLng([First Booth#]) And
CLng([Last
Booth#])))
ORDER BY [2 Contract: Booth Notification Report
Query_byBooth#].Sort;
-----Original Message-----
Post the SQL String of your Query.

--
HTH
Van T. Dinh
MVP (Access)


Van:

First, I would like to thank you for your response.

Second, Let me try and clarify the requirement to see IF
you have answered my question ....

(1) The "Range" Lower and Upper Limit is in response to
a "Prompt" (Example: Between [LowerLimit] AND
[UpperLimit] ... where does CLong go in this Code ... Edit
SQL View ???
(2) The Actual Values of the field I am working with, is
the Result of a "Val() Function). It starts as a "Text
Field" and is run thru the Val() Function.

Is you response based upon these requirements/conditions???

Again, I appreciate your help.

-----Original Message-----
If you work with whole (integral) numbers, try:

WHERE CLng([YourField]) BETWEEN LowerLimit AND UpperLimit

If it still doesn't work, try:

WHERE CLng([YourField]
BETWEEN CLng(LowerLimit) AND CLng(UpperLimit)

--
HTH
Van T. Dinh
MVP (Access)



"Donald Regener" <[email protected]>
wrote in message
I am developing a Query that requires a "Between
Operator"

The field requiring the Operator started as a "Text
Field". The field was converted to a Number Field by
use
of the Val() Function. Unfortunately the Data can be a
3, 4, or 5 character field (In the Native Table). I am
trying to Use the "Between Operator" with Prompts. The
Query works as desired with a "Fixed Value" (Number
Embedded in Between Statment" vice entered as part of
the "Prompt Response").

The problem is associated with the 3 and 4 character
data. IF the 3 and 4 character data were excluded
(>9999), the query would work as desired. When the 3
and
4 character data is included, the query results looks
like a "Text Search" on the first characters. The 3 and
4 Character Data is intermingled with the Correct 5
Digit
Data. (Example Between 10,000 and 10999 entered from
Prompts would return: 10130, 1024, 113, 11405, etc). I
have tried enclosing the Prompt field with a Val()
Function (From SQL View) and enclosing with the Trim ()
Function. Neither has provided the desired results.

I cannot change the 3 & 4 Character data to 5
Characters. I lost that request this morning. I do not
know how to fix it, but it appears the Prompt field has
some characters associate with it beside the ones typed
as the response to the Prompt.

All help Gladly accepted.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top