Data Type Problem Between Code and Query

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

Guest

I am using a function to convert a user input string to criteria for a query
field.
The input string is, for example, "456,3268,9754,25"
The function converts it to a string, "<> 456 or <> 3268 or <> 9754 or <> 25"
and it is then placed in a form field that has been listed in the query
criteria.
(The form is used to obtain input for several field criteria in the query,
and it stays open while the query is run.)
The table/query field for this converted criteria is a double, and I get
data type error messages.
How can I make these data types agree? (If I manually enter the same
characters directly into the query criteria field, it works.) This is in
Access 2003.
Thank you for your consideration.
 
I can't think of any circumstances in which
<> 456 or <> 3268 or <> 9754 or <> 25
could successfully be used as a criterion on a number field. Maybe you
mean
NOT IN (456, 3286, 9754, 25)

But I wouldn't trust that if the field in question is a Double, unless
you want to (e.g.) reject 25 but accept 25.00000001 and 24.9999999. So
maybe you need to wrap the field you're testing in CLng() so you're
comparing integers with integers.

If the users are allowed to enter decimals - and there doesn't seem
anything to stop them - it certainly won't work as expected.
 
1. You haven't shown us the code so it is hard to tell if the SQL is being
created properly. It helps to put a stop in the code so you can print the
completed SQL string in the immediate window and examine it.
2. Compound conditions MUST repeat the field name.
FldA = 1 or 2 ---- incorrect syntax
FldA = 1 OR FldA = 2
3. When you use negative tests in a compound condition, you need to connect
them with the AND operator rather than the OR operator. Even if your SQL
string were being created properly, it would not work. It would select ALL
records since each individual record cannot be two values at once.
Therefore if the value is 456 it cannot also be 3286 so the condition would
be true and no records would be excluded.
4. The easiest to read solution is to generate an In() clause. It is also
more compact than a long compound condition:
Where YourField NOT In(456,3268,9754,25);
 
Back
Top