Problem with using form fields for "From / To" purposes

  • Thread starter Thread starter Frank Ryan
  • Start date Start date
F

Frank Ryan

When using fields from a form in a SQL statement to limit
records selected, it acts as if we had "<" instead of "<="
associated with the "To" field. For example, if the form
values were "03-0001" and "03-0272" respectively, then we
would be selecting records with "03-0001" thru "03-0271".
If we used LITERAL values in the SQL statement, then the
QUERY worked correctly. We got around this by appending
a "0" (zero character) to the end of the "To" field.

As follows: ... "<=[Forms]![RForm]![ToValue] + '0'" ...

This workaround gives us the correct selection results.
My question is whether anyone else has come across this
situation and if so, is there a better solution?

One last note ... the form "From / To" field values were
always correctly shown in the Report Heading!
 
"03-0272" (for example) is a string - not a number.

"03-030" (for example) would sort *after* - not before - that.

Does that help?

TC
 
We verify that the "From" field is less than or equal to
the "To" field, using VBA. At this point, we feel there
is a bug in ACCESS 2002. There should not be the need to
append any characters to the end of the "To" field.

Again, if the SQL Query references form fields, then it
acts like we used "<" for the "To" field when in fact we
used "<=". The SQL Query will work if literal values are
used. However, we feel that form fields should be able to
be correctly referenced.

Hope this explanation helps. Thanks.
-----Original Message-----
"03-0272" (for example) is a string - not a number.

"03-030" (for example) would sort *after* - not before - that.

Does that help?

TC



Frank Ryan said:
When using fields from a form in a SQL statement to limit
records selected, it acts as if we had "<" instead of "<="
associated with the "To" field. For example, if the form
values were "03-0001" and "03-0272" respectively, then we
would be selecting records with "03-0001" thru "03- 0271".
If we used LITERAL values in the SQL statement, then the
QUERY worked correctly. We got around this by appending
a "0" (zero character) to the end of the "To" field.

As follows: ... "<=[Forms]![RForm]![ToValue] + '0'" ...

This workaround gives us the correct selection results.
My question is whether anyone else has come across this
situation and if so, is there a better solution?

One last note ... the form "From / To" field values were
always correctly shown in the Report Heading!
Click to expand...


.
Click to expand...
 
Frank, I doubt this is a bug. Can you post the full SQL text of the query?

TC


Frank Ryan said:
We verify that the "From" field is less than or equal to
the "To" field, using VBA. At this point, we feel there
is a bug in ACCESS 2002. There should not be the need to
append any characters to the end of the "To" field.

Again, if the SQL Query references form fields, then it
acts like we used "<" for the "To" field when in fact we
used "<=". The SQL Query will work if literal values are
used. However, we feel that form fields should be able to
be correctly referenced.

Hope this explanation helps. Thanks.
-----Original Message-----
"03-0272" (for example) is a string - not a number.

"03-030" (for example) would sort *after* - not before - that.

Does that help?

TC



Frank Ryan said:
When using fields from a form in a SQL statement to limit
records selected, it acts as if we had "<" instead of "<="
associated with the "To" field. For example, if the form
values were "03-0001" and "03-0272" respectively, then we
would be selecting records with "03-0001" thru "03- 0271".
If we used LITERAL values in the SQL statement, then the
QUERY worked correctly. We got around this by appending
a "0" (zero character) to the end of the "To" field.

As follows: ... "<=[Forms]![RForm]![ToValue] + '0'" ...

This workaround gives us the correct selection results.
My question is whether anyone else has come across this
situation and if so, is there a better solution?

One last note ... the form "From / To" field values were
always correctly shown in the Report Heading!
Click to expand...


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