Mid function in sql where clause - weird problem 
Author Message
 Mid function in sql where clause - weird problem

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  

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?

Kym Wilson

Sun, 21 Nov 1999 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Need Function to add Where Clause or Parse SQL

2. Problem with missing mid-function

3. problem using the MID function

4. Problem with MID function

5. Problems with Mid() Function.....

6. VB5 ODBC 3.5 MSACCESS: Problems with DBCS in the SQL WHERE Clause

7. Problem with SQL 'Like' Clause

8. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem

9. SQL : Where clause problems

10. SQL: Where clause problems...

11. Problems with SQL 'Like' clause

12. Error 3131 Syntax error in FROM clause - using SQL accessing external ODBC problem


Powered by phpBB® Forum Software