Error 3061 openrecordset fails

[exaccess]

The code below fails.

Dim ResultQy As String, qdf As QueryDef
    Dim ResultFm As String
    ResultQy = "ByFederationQy"
    Dim strSQL  As String               'sql statement to execute
    strSQL = "SELECT [MembersTbl].[Federation], [MembersTbl].[NOM], [MembersTbl].[PRENOM], " & _
        "[MembersTbl].[EMAIL], [MembersTbl].[LANGUAGE] " & _
        "FROM [MembersTbl] " & _
        "WHERE (([MembersTbl].[Federation]) = '(" & ORG & ")' ) " & _
        " ORDER BY [MembersTbl].[NOM], [MembersTbl].[PRENOM];"
    If QueryExists(ResultQy) = True Then
        CurrentDb.QueryDefs.Delete ResultQy
    End If
    Set qdf = CurrentDb.CreateQueryDef(ResultQy, strSQL)
    
    Dim rs As dao.Recordset
    Set rs = CurrentDb.OpenRecordset(ResultQy, dbOpenDynaset)

When I run the code it fails at the last line giving error message:

Runtime error 3061 - toofew parameters expected 1

What can be the problem?

 

[pr2-eugin]

Why could you simply not do this?

    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [MembersTbl].[Federation], [MembersTbl].[NOM], [MembersTbl].[PRENOM], " & _
             "[MembersTbl].[EMAIL], [MembersTbl].[LANGUAGE] " & _
             "FROM [MembersTbl] " & _
             "WHERE [MembersTbl].[Federation] = '" & ORG & "'" & _
             " ORDER BY [MembersTbl].[NOM], [MembersTbl].[PRENOM];"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)

 

[exaccess]

Why could you simply not do this?

    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [MembersTbl].[Federation], [MembersTbl].[NOM], [MembersTbl].[PRENOM], " & _
             "[MembersTbl].[EMAIL], [MembersTbl].[LANGUAGE] " & _
             "FROM [MembersTbl] " & _
             "WHERE [MembersTbl].[Federation] = '" & ORG & "'" & _
             " ORDER BY [MembersTbl].[NOM], [MembersTbl].[PRENOM];"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)

I have tried the code it says

Error 3061 too few parameters Expected 1

It fails at the last statement.

 

[Frothingslosh]

So what it's telling you is that it's expecting a parameter, and you haven't supplied one. Right now, what you're doing is submitting the contents of the variable "ORG" as a string and including it in the SQL statement. Is that correct? Is it possible that ORG can contain a quote or an apostrophe? If that's the case, try this:

"WHERE [MembersTbl].[Federation] = " & Chr(34) & ORG & Chr(34) & _

Edit: I also noticed that parentheses vanished between posts 1 and 2 - are they required to find a match in MembersTbl.Federation?

 

[exaccess]

So what it's telling you is that it's expecting a parameter, and you haven't supplied one. Right now, what you're doing is submitting the contents of the variable "ORG" as a string and including it in the SQL statement. Is that correct? Is it possible that ORG can contain a quote or an apostrophe? If that's the case, try this:

"WHERE [MembersTbl].[Federation] = " & Chr(34) & ORG & Chr(34) & _

Edit: I also noticed that parentheses vanished between posts 1 and 2 - are they required to find a match in MembersTbl.Federation?

Yes ORG contains a string of two chracters suc as SH, AR etc.. It comes as a parameter in a function call from another sub. I display it to be sure it contains the right values. No it does not contain quote or apostrophe. Nevertheless I tried your code it did not work. Same place same message. The parentheses vanished because the other expert wrote it like that it is not my posting. But I tried that also. It did not work. The point is to find all the records that match the field Federation.

 

[Frothingslosh]

And you've confirmed that the data in ORG is, indeed, being returned as a string, and before this part of your code runs?

The next thing I'd probably do is a quick check to make sure you have every table and field name spelled correctly. If anything is misspelled, it can throw this error.

 

[exaccess]

The next thing I'd probably do is a quick check to make sure you have every table and field name spelled correctly. If anything is misspelled, it can throw this error.

You are right. On double checking I found one of the fields namely Language was LANG in the table. Now it works. Thanks a lot.:http://images.access-programmers.co.uk/forums/images/smilies/smile.gif