By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,376 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

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

P: 4
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
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim rs As DAO.Recordset
  3. Dim strSQL As String
  4.  
  5. Set db = OpenDatabase(systemDbPath & "Data.accdb")
  6.  
  7. If ImportantDatesOnly = True Then
  8.     strSQL = "SELECT [etl].[TaskID], [qec].[EventID], [qec].[InstanceID], " & _
  9.              "IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) Or " & _
  10.              "([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null), " & _
  11.              "[qec].[EventStart],DateAdd([qec].[PeriodTypeID], " & _
  12.              "[qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart])), " & _
  13.              "IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, " & _
  14.              "[qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned], " & _
  15.              "[tblEventException].[InstanceComment], [qec].[EventStart], [qec].[RecurCount], " & _
  16.              "[qec].[PeriodFreq], [ltPeriodType].[PeriodType] FROM ((qryEventCartesian As qec LEFT JOIN " & _
  17.              "tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND " & _
  18.              "([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON " & _
  19.              "[qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON " & _
  20.              "[qec].[EventID] = [etl].[EventID] WHERE ((([etl].[TaskID]) = " & TaskID & ") And " & _
  21.              "(([qec].[IsImportant]) = True)) ORDER BY [qec].[EventID], [qec].[InstanceID];"
  22. Else
  23.     strSQL = "SELECT [etl].[TaskID], [qec].[EventID], [qec].[InstanceID], " & _
  24.              "IIf([tblEventException].[EventID] Is Null,IIf(([qec].[PeriodTypeID] Is Null) " & _
  25.              "Or ([qec].[PeriodFreq] Is Null) Or ([qec].[InstanceID] Is Null)," & _
  26.              "[qec].[EventStart],DateAdd([qec].[PeriodTypeID]," & _
  27.              "[qec].[InstanceID]*[qec].[PeriodFreq],[qec].[EventStart]))," & _
  28.              "IIf([tblEventException].[IsCanned],Null,[tblEventException].[InstanceDate])) AS EventDate, " & _
  29.              "[qec].[EventDescrip], [qec].[Comment], [tblEventException].[IsCanned], " & _
  30.              "[tblEventException].[InstanceComment], [qec].[EventStart], [qec].[RecurCount], " & _
  31.              "[qec].[PeriodFreq], [ltPeriodType].[PeriodType] FROM ((qryEventCartesian AS qec LEFT JOIN " & _
  32.              "tblEventException ON ([qec].[InstanceID] = [tblEventException].[InstanceID]) AND " & _
  33.              "([qec].[EventID] = [tblEventException].[EventID])) LEFT JOIN ltPeriodType ON " & _
  34.              "[qec].[PeriodTypeID] = [ltPeriodType].[PeriodTypeId]) RIGHT JOIN EventsTasksLink AS etl ON " & _
  35.              "[qec].[EventID] = [etl].[EventID] WHERE [etl].[TaskID] = " & TaskID & _
  36.              " ORDER BY [qec].[EventID], [qec].[InstanceID];"
  37. End If
  38.  
  39. Set rs = db.OpenRecordset(strSQL)
  40.  
Calculated Query that Works
Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
Calculated Query that Doesn't Work
Expand|Select|Wrap|Line Numbers
  1. 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];
  2.  
Error Received for Second Query
Expand|Select|Wrap|Line Numbers
  1. Run-time error '3061'
  2.  
  3. Too few parameters.  Expected 1.
  4.  
Dec 15 '11 #1

✓ answered by 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?

Share this Question
Share on Google+
7 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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?
Dec 15 '11 #2

P: 4
Yeah, it's on line 39.
Dec 15 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 15 '11 #4

P: 4
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.
Dec 15 '11 #5

TheSmileyCoder
Expert Mod 100+
P: 2,321
Im only asking again, cause it seems to be the most obvious explanation. You also checked that your production database has the field IsImportant?
Dec 15 '11 #6

P: 4
... 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.
Dec 15 '11 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 16 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.