Access 2000 Query using "Between Operator"

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Date: Fri, 30 Apr 2004 12:58:28 -0700

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

  • Re: Access 2000 Query using "Between Operator"
    ... > I am developing a Query that requires a "Between Operator" ... IF the 3 and 4 character data were excluded ... > have tried enclosing the Prompt field with a Val ...
    (microsoft.public.access.queries)
  • Re: Dumb Character Column
    ... because of the character data we get an invalid ... you could just exclude rows where the first char is not a digit or maybe ... A lot depends on the number of rows involved, what else the select query ...
    (comp.databases.oracle.server)
  • Re: Dumb Character Column
    ... because of the character data we get an invalid ... you could just exclude rows where the first char is not a digit or maybe ... A lot depends on the number of rows involved, what else the select query ...
    (comp.databases.oracle.server)
  • Re: Need help print access report from VB6
    ... > does not match the parameter name in the query. ... > letter, character for character. ... > Steve Clark, Access MVP ... >> I would like to see just the ONE prompt, ...
    (microsoft.public.access.queries)
  • Re: Searching field from right to left
    ... character ID. ... >> I want to have a query that will prompt for a value but ... >One more variation on the theme: ...
    (microsoft.public.access.queries)