SQL SELECT not accepted

[exaccess]

The query below is rejected saying it is not an SQL statement. I can not find the error. Help please.

 Dim strSQL As String
' Diplays MembersTBL
    strSQL = "SELECT MembersTbl.NOM, MembersTbl.PRENOM, MembersTbl.EMAIL, MembersTbl.LANG, " & _
        " MembersTbl.Federation " & _
        " FROM MembersTbl " & _
        " WHERE not isnull(MembersTbl.Federation) " & _
        " ORDER BY MembersTbl.NOM, MembersTbl.PRENOM;"
    DoCmd.RunSQL strSQL

 

[Galaxiom]

Try changing this:
WHERE not isnull(MembersTbl.Federation)

to:
WHERE MembersTbl.Federation Is Not Null

 

[pbaldy]

It probably isn't a problem with the SQL. You can only use RunSQL on an action query (append, update, delete, etc). That's a regular select query, so RunSQL won't run it. Depending on your need, you can open a recordset on the SQL, make it the source of a form or subform, etc.

 

[Galaxiom]

That's a regular select query, so RunSQL won't run it. .

That would definitely be the problem.

However what I said about Is Not Null instead of IsNull() should still be implemented because it performs better.

 

[pbaldy]

However what I said about Is Not Null instead of IsNull() should still be implemented because it performs better.

I agree with you. I should have clarified that when I said the SQL was okay.

 

[exaccess]

Re: SQL SELECT not accepted [SOLVED]

It probably isn't a problem with the SQL. You can only use RunSQL on an action query (append, update, delete, etc). That's a regular select query, so RunSQL won't run it. Depending on your need, you can open a recordset on the SQL, make it the source of a form or subform, etc.

That was the key point. I made a query from the sql statement and made it the RecordSource of a form containing the required fields. That was the solution. Thanks to all contributions.

 

[pbaldy]

We were happy to help!