Error 3061 Too few Parameters. Expected 1

Anonymous
2011-09-10T21:05:32+00:00

I'm using a Union query as a Source for an Append query.  I will leave the code here.  tblGLTransactions and tblGLTranaactionNo. I don't know if that is causing a problem.  The tblGLTransactions has 2 fields, the fiest field GLTransactionNo is an autonumber field, so I didn't append to that field.  I need both tables because of the relationship for a form and subform for GLTransactions.  The entry I'm trying to append closes the year end.  Well, it should.

I should post the union query as well.   I have drained out John Vinson who has been helping my like crazy in the past two days.  This is the final step in completing this database and I cannoyt believe it crashes.

Here is the Union query ......I changed it.  No AccountTypeID and no AcctgDate.  IT's been modified for the extra fields. EDITED 09/12/11 12:51

SELECT qryYearEndReBalanceSheetTotals.AccountID, IIf([TotalTrans]<0,[TotalTrans]*-1,0) AS Debit, IIf([TotalTrans]>0,[TotalTrans],0) AS Credit, 0 AS GLTransactionNo, "Closing Entry" AS Source,qryYearEndReBalanceSheetTotals.TransDate FROM qryYearEndReBalanceSheetTotals

UNION ALL SELECT  ([Forms]![frmYrEndProcessing]![cboRetainedEarnings]) AS AccountID, IIf([SumOfProfit]<0,[SumOfProfit],0) AS Debit, IIf([SumOfProfit]>0,[SumOfProfit],0) AS Credit, 0 AS GLTransactionNo, "Closing Entry" AS Source, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") AS TransDate FROM qryYearEndBalanceSheetProfit;

 Here is the code:  I created another query with GLTansactionNo and AcctgDate.   The same 2 fields as the table.  It still fails with the same Error 3061. Like the Title

   Thanks for any help.  this coded edited Sunday 09/12/11 12:54 pm

Private Sub cmdOkay_Click()

On Error GoTo ErrorHandler

    Dim dbs As DAO.Database

    Dim rst As DAO.Recordset

    Dim strSQL As String

    Dim lngID As Long

    Dim strMessage As String

    Dim IntMessageDialog As Integer

    Dim strTitle As String

    Dim intResult As Integer

     If Me!chkPrintFinancials = False Then

     MsgBox ("Print Financial Statements before Closing the Year End." & vbCrLf & _

     "Ensure Start and Fiscal Year End Dates are not Blank in My Company Information Form.")

     Else

                        If Nz(Me![cboRetainedEarnings].Value) = "" Then

                        MsgBox ("Choose an Account for Retained Earnings.")

                    End If

                End If

                    If Not Me.chkPrintFinancials = False And Me.cboRetainedEarnings > -1 Then

                   Me.cmdCancelAndExit.Enabled = False

                Else: Exit Sub

            End If

         DoCmd.Beep

                strMessage = "Are you sure you want to Close the Year End?"

                IntMessageDialog = vbQuestion + vbYesNo + vbDefaultButton1

                strTitle = "Confirm Year End Close"

                intResult = MsgBox(strMessage, IntMessageDialog, strTitle)

                        If intResult = vbNo Then

                                Exit Sub

                        End If

                        If intResult = vbYes Then

                            If IsNull(DLookup("FiscalYearEndDate", "tblMyCompanyInfo")) Then

                       MsgBox "Please fill in the fiscal year end date in the My Company info form", vbOKOnly

                        Exit Sub

                            Else

        strSQL = "INSERT INTO [tblGLTransactions] ( AcctgDate) SELECT AcctgDate FROM " _

        & "[qryYearEndGLTransactions] ;"

        CurrentDb.Execute strSQL, dbFailOnError

        lngID = DMax("GLTransactionNo", "tblGLTransactions")

        strSQL = "INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit," _

        & " Source, GLTransactionNo)" _

        & "SELECT TransDate, AccountID, Debit, Credit," _

        & "Source, " & lngID & " As GLTransactionNo FROM [qryuniYearEndFinalClosingEntry] ;"

        CurrentDb.Execute strSQL, dbFailOnError

        DBEngine(0)(0).Execute "qupdYearEndDateChanges", dbFailOnError

         MsgBox ("The Year End is Processed.")

    End If

End If

       DoCmd.Close acForm, Me.Name

        DoCmd.OpenForm "frmEnterGLTransactions"

        DoCmd.GoToRecord , , acLast

ErrorHandlerExit:

   Exit Sub

ErrorHandler:

   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description

  Debug.Print strSQL

   Resume ErrorHandlerExit

End Sub

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2011-09-18T17:45:44+00:00

I will reply, Rob, if only to wish you good luck.  I wish I'd been able to resolve the issue for you, but I think we'd just be herding those kittens for evermore I'm afraid.

One final point of detail.  I'd recommend:

"#" & Format(Now(),"yyyy-mm-dd hh:nn:ss") & "#" & _

By using the ISO standard for date and time notation it caters for whatever Windows local date format is in use.  As you'd written it, it would fail on my system for instance as the UK short date format is dd/mm/yyyy, so 4th July would be changed to 7 April, which your neighbours to the south would not appreciate.

Was this answer helpful?

0 comments
Answer accepted by question author
Anonymous
2011-09-12T23:19:56+00:00

Mea culpa. I left out spaces in two places.  BEFORE SELECT and BEFORE FROM

strSQL = "INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit," _

        & "Source, GLTransactionNo)" _

        & " SELECT TransDate, AccountID, Debit, Credit, Source," & lngID _

        & " FROM [qryuniYearEndFinalClosingEntry] ;"

        CurrentDb.Execute strSQL, dbFailOnError

If lngId is equal to 33, then Debug.Print should return

INSERT INTO [tblTransactions] (TransDate, AccountID, Debit, Credit,Source, GLTransactionNo) SELECT TransDate, AccountID, Debit, Credit, Source,33 FROM [qryuniYearEndFinalClosingEntry] ;

Was this answer helpful?

0 comments

69 additional answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-09-16T02:19:50+00:00

    Oh well, I just tried to run the append query in code that Imentioned in the last post where I used DMax function in the Union query I changed.

    I got an erro 3061 Too few parameters. Expected 1.

    I did it on a backup copy and I changed everything back.  It kind of baffled me that when you run it from the query design grid it works.

    So why would it not work in code?

    I'm beyond baffled.

    Everything is back to the same.  I put the code back in to the backup with strSql you created ken.

    The strDate and DMax lines a re back again.

    The only thing I was wondering about is that in the immediate window it shows  Closing Entry like this   "Closing Entry'   single quotes   and then "Closing Entry"  with double quotes.

    Do you think it may not like that.

    That was an earlier post where JohnSpencer pointed that out and used "Closing Entry'  with single quotes.

    I'm not sur if Access expects the Source field's data to be identical.

    I assume it would be stored as Closing Entry without any quotes.

    Just something to ask, I could be wrong, but perhaps you know.  At this point I really believ it may be something minor.  I tried looking at in Word like you suggested,  I couldn't find anything.  It wouldn't tabke IIf statement aabd stor it all as text would it?

    I'm get way out thinking that.

    ?? what can I do?

    Rob

    Was this answer helpful?

    0 comments
  2. Anonymous
    2011-09-16T18:15:21+00:00

    ken,

    I'm just thinking about the calculated field TotalTrans.  I sent a copy of the source for qryYearEndReBalanceSheetTotals.

    SELECT tblChartOfAccounts.AccountID, Sum(IIf(IsNull([Debit]),0,[Debit])-IIf(IsNull([Credit]),0,[Credit])) AS TotalTrans, tblChartOfAccounts.AccountNum, DLookUp("FiscalYearEndDate","tblMyCompanyInfo") AS TransDate, "Closing Entry" AS Source, 0 AS GLTransactionNo

    FROM tblChartOfAccounts INNER JOIN qryYearEndBalanceSheetTrans ON tblChartOfAccounts.AccountID = qryYearEndBalanceSheetTrans.AccountID

    GROUP BY tblChartOfAccounts.AccountID, tblChartOfAccounts.AccountNum, DLookUp("FiscalYearEndDate","tblMyCompanyInfo"), "Closing Entry", 0, tblChartOfAccounts.AccountTypeID

    HAVING (((tblChartOfAccounts.AccountTypeID)>=4 And (tblChartOfAccounts.AccountTypeID)<=6))

    ORDER BY tblChartOfAccounts.AccountID, tblChartOfAccounts.AccountTypeID;

    I asked a question about AccountID and you sent me the explanation regarding lookup fields Septenber 15/11.

    But what I'm wondering about is that if you read the above sql.  I will underline the line now.

    As TotalTrans.  TotalTrans is a calculated field.  It creates a sum for each account in the query above.

    Then I created the  Union query where I use 2 IIf statements evaluating TotalTrans, 1 AS Debit and the other AS Credit  from qryYearEndReBalanceSheetTotals.

    Then In the UNION ALL SELECT in the query I use 2 more iif's evaluating SumOfProfit AS Debit and AS Credit.  I mentioned earlier that SumOFprofit is the sum of all TotalTrans.

    So, bascially this calculated field,[TotalTrans]  is not mentioned in the Union Query.  I didn't mention it or refer to it because, it isn't a field in tblTransactions.

    I'm just wondering if I use it to calculate the values for the Union query with out selecting it From the query.    ie. qryYearEndReBalanceSheetTotals.TotalTrans

    could I be leaving something out.  But this wouldn't be a syntax error 3131 syntax in FROM clause would It?

    I hope you understnad what I'm asking or trying to say?

    Thanks ken,

    Rob

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-09-16T21:22:21+00:00

    Im not sure about this but, I think this may be the problem with the query.

    This is the source for qryYearEndEndBalanceSheetProfit in the Union Query.

    SELECT Sum(qryYearEndBalanceSheetIncome.TotalTrans) AS SumOfProfit

    FROM qryYearEndBalanceSheetIncome;

    Now, qryBalanceSheetIncome has 2 more fields.   AccountNum and AccountID.

    If I were to join AccountID in the above mentioned query, the results would return data for the Sum of AccountID's,.

    I used the frmYrEndProcessing!cbofrmRetainedEarnings  value in an AS AccountID to get the AccountID for SumOfProfit.

    Since, SumOfProfit is an Equity AccounType or AccountTypeID 3, it would never return in a query I produce, because I used a criteria for AccountTpeID >=4 and <=6.

    These are the accounts that need to be closed, so this is correct.

    But my thinking is that when I ran the query just to get those accounts, get a sum for each accountID affected, I can then sum all of those accounts.  (The query above) to arrive at net profit .

    I need to also close net profit (which is AccountType =3), so I create the Union Query for this reason.

    If I just assign a value, like I did when the user choose the accountID from the combo box, it will not be in any query AccountID value in the queries.

    My real question is can I do what I did, by using Forms!frmYrEndProcessing!RetainedEarnings AS AccountID.

    In essence, it will not be in any query, but supplied by the user.  And because my queries start with filtering data with AccountTypeID's >=4 and <=6, would this cause a poblem in the Union I am trying to produce.

    Keep in mind, that the Union query results ar correct.  I'm not sure, but isn't this where a Union query would be used? I'm thinking about many things.

    Is a self-join required and can you have a self join in a query, if yes, how?

    The form's recordsource is a query,  Does this query affect code.

    The recordsource is all accounts with AccountTypeID 3  (Equity accounts), thus the combo box with the same recordsource.

    If you think I' doing the union query the wrong way, let me know.

    Again, thanks an dif you're tired of this I understand,

    Rob

    Was this answer helpful?

    0 comments