too few paramaters, expected 1 - dao recordset (1 Viewer)

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
Hi all,

I am trying to open a record set based on a query and then with the results of the query, post 2 values in to 2 variables for use later on in a module.

My code so far is:

Code:
            Dim strBanksianWinner1 As String
            Dim strBanksianWinner2 As String
            Dim strSelect1 As String
            Dim rs As DAO.Recordset
            Dim strSQL As String
                
                
                
                strSelect1 = "SELECT TOP 2 tblBanksianWinners.chrEntrant" _
                            & " FROM tblBanksianWinners" _
                            & " ORDER BY tbBanksianWinners.dtmShowDate DESC;"
                
                strSQL = strSelect1
                MsgBox strSQL 'for debugging purposes
                
                Set rs = CurrentDb.OpenRecordset(strSQL)
                rs.MoveFirst
                strBanksianWinner1 = rs.Fields("chrEntrant")
                rs.MoveLast
                strBanksianWinner2 = rs.Fields("chrEntrant")
                rs.Close
                
                Set rs = Nothing

But it doesn't work and I don't understand! All it is is a text field

I would value your assistance.

Many thanks,
Simon
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
My guess is that the table or one of the field names is misspelled.
 

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
Hi, yeah I looked for that but I have just looked again and found a sp mistake in the order by statement. Thanks!
 

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
And that now works! Thanks very much.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
Hi there,

Ok, so I found the typo and it fixed it but I then had to adjust my code to make it more well-rounded by putting some qualifiers in:

My code is this:

Code:
Case Is = "Banksian Medal" 'Most points in horticultuaral classes. Can only be won by same person every 3 years.
            Dim strBanksianWinner1 As String
            Dim strBanksianWinner2 As String
            Dim strBanksianSelect As String
            Dim strBanksianWhere As String
            'Dim strSelect As String
            Dim rs As DAO.Recordset
            Dim strBanksianSQL As String
            'Dim strBanksianLinkCriteria As String
                
                
                '************************************************************************************
                'First of all, make a note of the past two winners to discount them from the runnings
                'This will select the two most recent entries from the tblBanksianWinners table.
                '************************************************************************************
                
                
                strBanksianSelect = "SELECT TOP 2 tblBanksianWinners.dtmShowDate, " _
                                    & "tblBanksianWinners.chrEntrant, " _
                                    & "tblShow.dtmShowDate, " _
                                    & "DatePart('yyyy',[tblShow].[dtmShowDate]) AS ShowYear " _
                                    & "FROM tblShow INNER JOIN tblBanksianWinners ON " _
                                    & "tblShow.dtmShowDate = tblBanksianWinners.dtmShowDate"
                            
                
                strBanksianWhere = " WHERE (ShowYear Between " & Me.numShowYear - 2 _
                            & " AND " & Me.numShowYear & ") AND " _
                            & "(ShowYear <> " & Me.numShowYear & ")" _
                            & " ORDER BY tblBanksianWinners.dtmShowDate DESC;"
                            
                
                strBanksianSQL = strBanksianSelect & strBanksianWhere
                MsgBox strBanksianSQL 'for debugging purposes
                
                Set rs = Nothing
                Set rs = CurrentDb.OpenRecordset(strBanksianSQL)
                rs.MoveFirst
                strBanksianWinner1 = rs.Fields("chrEntrant")
                rs.MoveLast
                strBanksianWinner2 = rs.Fields("chrEntrant")
                rs.Close
                             
                Set rs = Nothing

I get the same error as above when I include the where statement, but when I use only the select statement, it works fine.

Is anyone able to spot a problem with the code please?

Basically I have a form and when I click the calculate button, it selects the top two entries in a table where it is between two this year and two years ago making sure that this year is not included in the mix. It then returns those two results and puts each in a variable and outputs them to screen.

The message box of the SQL statement is read as follows:

 
Last edited:

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
Never mind!

I've fixed it with this code for the where clause instead:

Code:
strBanksianWhere = " WHERE ((((DatePart('yyyy',[tblShow].[dtmShowDate])) between " & Me.numShowYear - 2 _
                            & " AND " & Me.numShowYear & ")" _
                            & " AND (DatePart('yyyy',[tblShow].[dtmShowDate])) <> " & Me.numShowYear & "))" _
                            & " ORDER BY tblBanksianWinners.dtmShowDate DESC;"

Thanks,
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
This is a good way to debug:

http://www.baldyweb.com/ImmediateWindow.htm

but my gut feeling, presuming there are no misspellings, is the alias is the culprit. Typically you can't use an alias (like ShowYear) in the WHERE clause, due to the order in which the clauses are evaluated. Try coding it so you end up with:

WHERE DatePart('yyyy',[tblShow].[dtmShowDate]) Between...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
Ah, my slow typing fails me again. :p
 

Mr_Si

Registered User.
Local time
Today, 23:33
Joined
Dec 8, 2007
Messages
163
Thanks though for your response, it's useful to know!
 

Users who are viewing this thread

Top Bottom