Run-time error '3061' Too few parameters. Expected 1. Can't figure this out.

[bunchaCoconuts]

I'm trying to figure out why I'm getting this error. I'm relatively inexperienced. The nature of the following code is that a slightly different query is run whether or not the "ImportantDatesOnly" flag is true or not. The problem is that the query that runs while the flag is true fails, and the query for when the flag is false does not fail. The error occurs at db.OpenRecordset (last line in the code segment). The failing query does run fine when cut and paste directly into a new query and then run. I've underlined the sections that are different below. This is in Access 2007.


Code Segment

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
Set db = OpenDatabase(systemDbPath & "Data.accdb")
 
If ImportantDatesOnly = True Then
    strSQL = "SELECT [etl].[TaskID], [qec].[EventID], [qec].[InstanceID], " & _
             "IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) Or " & _
             "([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null), " & _
             "[qec].[EventStart],DateAdd([qec].[PeriodTypeID], " & _
             "[qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart])), " & _
             "IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, " & _
             "[qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned], " & _
             "[tblEventException].[InstanceComment], [qec].[EventStart], [qec].[RecurCount], " & _
             "[qec].[PeriodFreq], [ltPeriodType].[PeriodType] FROM ((qryEventCartesian As qec LEFT JOIN " & _
             "tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND " & _
             "([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON " & _
             "[qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON " & _
             "[qec].[EventID] = [etl].[EventID] WHERE ((([etl].[TaskID]) = " & TaskID & ") And " & _
             "(([qec].[IsImportant]) = True)) ORDER BY [qec].[EventID], [qec].[InstanceID];"
Else
    strSQL = "SELECT [etl].[TaskID], [qec].[EventID], [qec].[InstanceID], " & _
             "IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) " & _
             "Or ([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null)," & _
             "[qec].[EventStart],DateAdd([qec].[PeriodTypeID]," & _
             "[qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart]))," & _
             "IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, " & _
             "[qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned], " & _
             "[tblEventException].[InstanceComment], [qec].[EventStart], [qec].[RecurCount], " & _
             "[qec].[PeriodFreq], [ltPeriodType].[PeriodType] FROM ((qryEventCartesian AS qec LEFT JOIN " & _
             "tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND " & _
             "([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON " & _
             "[qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON " & _
             "[qec].[EventID] = [etl].[EventID] WHERE [etl].[TaskID] = " & TaskID & _
             " ORDER BY [qec].[EventID], [qec].[InstanceID];"
End If
 
Set rs = db.OpenRecordset(strSQL)
 

Calculated Query that Works

SELECT [etl].[TaskID],[qec].[EventID],[qec].[InstanceID], IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) Or ([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null), [qec].[EventStart],DateAdd([qec].[PeriodTypeID],[qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart])), IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, [qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned],[tblEventException].[InstanceComment], [qec].[EventStart],[qec].[RecurCount],[qec].[PeriodFreq],[ltPeriodType].[PeriodType] FROM ((qryEventCartesian As qec LEFT JOIN tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND ([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON [qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON [qec].[EventID] = [etl].[EventID] WHERE (([etl].[TaskID]) = 8) ORDER BY [qec].[EventID], [qec].[InstanceID];
 

Calculated Query that Doesn't Work

SELECT [etl].[TaskID], [qec].[EventID], [qec].[InstanceID], IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) Or ([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null), [qec].[EventStart],DateAdd([qec].[PeriodTypeID], [qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart])), IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, [qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned], [tblEventException].[InstanceComment], [qec].[EventStart], [qec].[RecurCount], [qec].[PeriodFreq], [ltPeriodType].[PeriodType] FROM ((qryEventCartesian As qec LEFT JOIN tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND ([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON [qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON [qec].[EventID] = [etl].[EventID] WHERE ((([etl].[TaskID]) = 8) And (([qec].[IsImportant]) = True)) ORDER BY [qec].[EventID], [qec].[InstanceID];
 

Error Received for Second Query

Run-time error '3061'
 
Too few parameters.  Expected 1.
 

[TheSmileyCoder]

I've spent 30 minutes looking them both over, but I can't spot anything.

Just to make sure, you are getting the error on line 39, correct?

[bunchaCoconuts]

Yeah, it's on line 39.

[TheSmileyCoder]

Well the "dummy" question it is then.
Are you sure you spelled the field IsImportant correct?

Is it a Yes/No type field?

This shouldn't matter but Ill ask anyway for lack of anything better to suggest: Does the field always have a value? I.e. its always either True or False, but never null.

[bunchaCoconuts]

I just double, and triple checked those things. It is typed correctly, it is a yes/no, and the default value is false.

Here is some more information that will hopefully help:

This database is split. I have a development environment and a production environment for this database. In the development environment, the data is stored on the local hard drive. The production environment stores the front-end on the local hard drive, but the data is stored on a network drive. While double checking these things you brought up, I realized the error only occurs in the production environment. So something about flipping the switch to use the data stored on the network drive (which is stored in the systemDbPath variable on line 5 in the code above) is causing this query to fail. I have no idea what that could be though.

[TheSmileyCoder]

Im only asking again, cause it seems to be the most obvious explanation. You also checked that your production database has the field IsImportant?

[bunchaCoconuts]

... I had the stored query in both the front-end and the data file. The "IsImportant" field existed in the front-end, but not the data file. Thank you so much, sorry to waste your time on such a trivial issue.

[NeoPa]

We see many such questions, so don't worry about that, but you may want to consider How to Debug SQL String (As that's always avoidable).

PS. You can scratch that. I'll leave this here as your question is a good illustration of exactly how it should be done. You, my friend, have absolutely nothing to reproach yourself for.