This problem occurs with VB5, Access 97 mdbs and the DAO 2.5/3.5
compatibility library.

I have a table which includes a number of fields which take integer
values representing different values of a class. IN order to allow the
user to select different combinations of the class values I am using a
sql statement of the form

data1.recordsource = "select * from details where  
(mid('NNNYNYN',[xsect],1)='Y');"
data1.refresh

The idea being that the value of xsect points to a character in the
yes/no string and by setting the yesses you can extract the classes.

xsect can have values from 1 to 7 and when testing for yes/no strings

"YNNNNNN", "NYNNNNN" etc the correct record set is returned for the
first 4 values.

When "NNNNYNN" is used an error occurs. - Run time error % invalid
procedure call on the refresh statement.

However "YNNNYNN" correctly returns records with values of 1 or 5.

I initially used spaces rather than N's but it made no difference.

For another variable a similar statement would only return values if
there was a Y in position 4. eg "NNNYY" would return 4 and 5 records but
"NNNNY" would return an empty set.

This is really weird and I can't explain why the failures are occur as
they are.

Any Ideas?

Thanks
Kym Wilson