SQL error help - run-time error 3601 too few parameters

[neov]

expected 1

ok guys, I could really use some help with this one...

This error message comes up in an Access 2000 file - when I hit debug, here is the code..

Function WriteFSCash()
Dim Myset As Recordset, MyDb As Database, SqlQuery As String, qdf As QueryDef
Dim s1 As String, s2 As String, s3 As String, s4 As String, s5 As String
Dim qcq As String, SqlQuery1 As String, myset1 As Recordset, i As Integer, Rec_count As Integer
Dim ARPer As String, ARYear As String, x As Integer
qcq = Chr(34) & "," & Chr(34)
'**************************************************************************************
'******************* Quote Comma Quote Variable  **************************************
'**************************************************************************************
1 ARPer = InputBox("Enter Current Period", "Post Lockbox Receipts")
If Len(ARPer) < 2 Then
    ARPer = "0" & ARPer
ElseIf Len(ARPer) > 2 Or ARPer = "" Or IsNull(ARPer) Then
    x = MsgBox("Invalid period entered! ", vbCritical)
    GoTo 1
End If

If Val(ARPer) <> Month(Now()) Then
    x = MsgBox("You entered" & ARPer & " for the period.  The current period is " & Month(Now()) & "Do you want to continue?", vbYesNo)
    If x <> 6 Then
        
        GoTo 1
    End If
End If
ARYear = InputBox("Enter Current Year", "Post Lockbox Receipts")
If Len(ARYear) > 2 Then
    ARYear = Right(ARYear, 2)
ElseIf Len(ARYear) < 2 Or ARYear = "" Or IsNull(ARYear) Then
    x = MsgBox("Invalid period entered! ", vbCritical)
    GoTo 1
End If

If ARYear <> Right(Year(Now()), 2) Then
    x = MsgBox("You entered " & ARYear & " for the year.  The current year is " & Year(Now()) & "Do you want to continue?", vbYesNo)
    If x <> 6 Then
       
        GoTo 1
    End If
End If

Open "t:\amy\ARImp.txt" For Output As #1
Print #1, Chr(34) & "ARCD00" & qcq & qcq & qcq & qcq & qcq & qcq & qcq & "C" & Chr(34)
Print #1, Chr(34) & "ARCD01" & qcq & qcq & qcq & qcq & qcq & qcq & qcq & "C" & qcq & qcq & "03" & qcq & qcq & qcq & qcq & qcq & ARPer & qcq & ARYear & Chr(34)
Set MyDb = CurrentDb
SqlQuery1 = "SELECT [Lockbox Detail Overflow].InvoiceNumber, [Lockbox Detail Overflow].InvoiceAmt, [Lockbox Detail Overflow].CustId, [Lockbox Detail Overflow].FSInvoiceAmt FROM [Lockbox Detail Overflow] WHERE ((([Lockbox Detail Overflow].ProcCode) = No)) ORDER BY [Lockbox Detail Overflow].InvoiceNumber;"
Set myset1 = MyDb.OpenRecordset(SqlQuery1, dbOpenDynaset)
If myset1.RecordCount > 0 Then
    myset1.MoveLast
    Rec_count = myset1.RecordCount
    myset1.MoveFirst
    
    For i = 1 To Rec_count
        If myset1!InvoiceNumber <> 0 Then
            SqlQuery = "SELECT AR_INVOICETAX.AR_IVC_NO, AR_INVOICETAX.IVC_TYPE, AR_INVOICETAX.CUST_ID, AR_INVOICETAX.TOT_FRGHT, AR_INVOICETAX.TOT_LESS, AR_INVOICETAX.TOT_OTHER, AR_INVOICETAX.TOT_SALES, AR_INVOICETAX.TAX_AUTHO, AR_INVOICETAX.TAX_CODE, AR_INVOICETAX.TAX_EXT_AM, AR_INVOICETAX.IV_TAX_RAT, AR_INVOICETAX.TAX_BAS_AM, AR_INVOICETAX.TAX_ACC_NO, AR_INVOICETAX.TAX_AMOUNT, AR_INVOICETAX.TAX_MASK, AR_INVOICETAX.NEW_INV FROM AR_INVOICETAX WHERE (((AR_INVOICETAX.AR_IVC_NO)='0" & myset1!InvoiceNumber & "'));"

Set Myset = MyDb.OpenRecordset(SqlQuery, dbOpenSnapshot)

      If Myset.RecordCount > 0 Then
                myset1.Edit
                myset1!fsInvoiceAmt = Myset!tot_sales + Myset!TOT_FRGHT + Myset!TOT_LESS + Myset!TOT_OTHER + IIf(Not IsNull(Myset!tax_amount), Myset!tax_amount, 0)
                myset1!CustId = Myset!cust_id
                myset1.Update
            End If
        End If
        myset1.MoveNext
    Next i
End If
x = MsgBox("Run BEXE LB1 in Fourth Shift to Create a Cash Set.  Then run the Create Application Macro in Access.", vbCritical)
Close #1
End Function

The bolded text is what is highlighted when I look at the code from hitting 'debug'.

Any ideas? Any help is greatly appreciated.

 

[neov]

ok, figured out that the bold doesn't work inside of the code param...

thanks again for any help....

 

[neov]

anyone?

 

[bat17]

Sorry not a answer, but you could try printing out you SqlQuery variable in the debug window, then copy the SQL code into a query and see if that runs OK. This is something I often need to do when I work in SQL

Peter

 

[neov]

bat, can you give me some detail on how to do that exactly? Thanks.

 

[bat17]

run the code to where it breaks and select debug.
press Ctr-G to get the debug window up.

type
Print SqlQuery
This should then print out what is in the sql code
Copy this code.
Got to the query tab and create a new query, dont select a table for it and it will open up in SQL view.
paste your code and try running it. also look at the query it creates in the normal design mode to see if it is what you expected.
if it is wrong change it to what it should be then look at the SQLcode to see what the difference is

Peter

 

[neov]

thx...

when I did the print Sql part, it appears to have left off part of the parameter.....it printed this [c]SELECT AR_INVOICETAX.AR_IVC_NO, AR_INVOICETAX.IVC_TYPE, AR_INVOICETAX.CUST_ID, AR_INVOICETAX.TOT_FRGHT, AR_INVOICETAX.TOT_LESS, AR_INVOICETAX.TOT_OTHER, AR_INVOICETAX.TOT_SALES, AR_INVOICETAX.TAX_AUTHO, AR_INVOICETAX.TAX_CODE, AR_INVOICETAX.TAX_EXT_AM, AR_INVOICETAX.IV_TAX_RAT, AR_INVOICETAX.TAX_BAS_AM, AR_INVOICETAX.TAX_ACC_NO, AR_INVOICETAX.TAX_AMOUNT, AR_INVOICETAX.TAX_MASK, AR_INVOICETAX.NEW_INV FROM AR_INVOICETAX WHERE (((AR_INVOICETAX.AR_IVC_NO)='01'));
[\c]

If you look at the section in the original post above the bolded section, you can see that there is an additional part that was left off.....any idea why?

 

[bat17]

It looks OK to me, if you a refering to the " & myset1!InvoiceNumber & " bit, that is what puts the number 1 in the query.

Did you try pasting it into a query to see if it would run OK?

I will try to create a table and test it tomorroe, bed time here now.

Peter