Re: Access 2000 Query using "Between Operator"

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Donald Regener (anonymous_at_discussions.microsoft.com)
Date: 05/01/04 

Date: Sat, 1 May 2004 06:51:45 -0700

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" <anonymous@discussions.microsoft.com>
wrote in message
>news:6ba901c42eed$8166a090$a601280a@phx.gbl...
>> 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.
>>
>
>
>.
>

Relevant Pages