Results 1 to 7 of 7
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Too few parameters expectede 1 in a Passthrough

    This sql statement is built in vba - if I paste this statement in a query window in SQL Studio it runs and returns one row wi the total

    select MAX(processorName) as procName, SUM(TransactionAmount) as batchAmount, processors.id from processors , transactions where processors.id = 87 and transactions.transactionType_id = 3 and ProcessedOn >= '5/1/2010' and ProcessedOn <= '9/22/2010' Group by processors.id order by ProcName
    the Debug.Print qdfPassThrough.sql returns the above statement


    but when I run it as a passthrough it fails with Too few parameters
    but the when I paste this into a query sql window in access it runs. Go figure

    select MAX(processorName) as procName, SUM(TransactionAmount) as batchAmount, processors.id from processors , transactions where processors.id = 87 and transactions.transactionType_id = 3 and ProcessedOn >= '5/1/2010' and ProcessedOn <=' 9/22/2010' Group by processors.id order by ProcName


    here is the code to create the query

    Public Function ProcessorPaymentsReport()

    Dim db As Database
    Dim qdfPassThrough As QueryDef
    Dim qdfTemp As QueryDef
    Dim rst As Recordset
    Dim rstTmpProcessor As DAO.Recordset
    Dim timestamp As Single
    Dim strSQL As String
    Dim strRecordset As String
    'Dim dtstatdate As Date
    'Dim dtenddate As Date
    strRecordset = "Select * from tempProcessPaymnetsSFSAmount"
    Set db = CurrentDb
    Set rstTmpProcessor = db.OpenRecordset(strRecordset, dbOpenDynaset)
    dtStartDate = [Forms]![modal_getdaterange]![textDateStart]
    dtenddate = [Forms]![modal_getdaterange]![textDateEnd]

    timestamp = Replace(Timer, ".", "")
    Set qdfPassThrough = db.CreateQueryDef("tempPassThroughQuery" & timestamp)
    qdfPassThrough.sql = "select MAX(processorName) as procName, SUM(TransactionAmount) as batchAmount, " _
    & "processors.id " _
    & "from processors , transactions " _
    & "where processors.id = 87 and transactions.transactionType_id = 3 and " _
    & "ProcessedOn >= " & "'" & dtStartDate & "'" & " and ProcessedOn <= " & "'" & dtenddate & "'" & " " _
    & "Group by processors.id " _
    & "order by ProcName"
    Debug.Print qdfPassThrough.sql
    'db.Execute qdfPassThrough
    qdfPassThrough.ReturnsRecords = True
    Set rst = qdfPassThrough.OpenRecordset(dbOpenSnapshot)
    If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
    rstTmpProcessor.AddNew
    rstTmpProcessor!processorName = rst!processorName
    rstTmpProcessor!batchamount = rst!batchamount
    rstTmpProcessor!id = rst!id
    rstTmpProcessor.Update
    rst.MoveNext
    Loop
    End If

    End Function
    Last edited by axsprog; 09-22-10 at 16:51.
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Not sure of how Access handles the conversion here, try using:
    Code:
    & "ProcessedOn >= " & "#" & dtStartDate & "#" & " and ProcessedOn <= " & "#" & dtenddate & "#" & " " _
    Have a nice day!

  3. #3
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Do you need to include dbSQLPassThrough as a parameter in

    Set rst = qdfPassThrough.OpenRecordset(dbOpenSnapshot, dbSQLPassThrough)

    Just a thought.

    C

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I can't get it to take the parameter or the hardcoded date - but when I paste that exact same syntax in SQL Studio Manager query builder - it runs
    Dale Houston, TX

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    data type mismatch creating a passthrough

    I get a data type mismatch in criteria when if I paste this exact statement in the query window in studio manager it runs!!!

    wtf?????
    This is the statement that is built as you will see in the code - but from vba it does not run

    SELECT sum(transactions.TransactionAmount)as batchamount , processors.processorName, transactions.ProcessedOn FROM processors, transactions where processors.id = 87 and transactions.transactionType_id = 3 AND transactions.ProcessedOn >= '6/1/2010' AND transactions.ProcessedOn <= '9/23/2010' group by transactions.ProcessedOn ,processors.processorName

    here is the start of the code

    Dim db As Database
    Dim qdfPassThrough As QueryDef
    Dim qdfTemp As QueryDef
    Dim rst As DAO.Recordset
    Dim rstTmpProcessor As DAO.Recordset
    Dim timestamp As Single
    Dim strSQL As String
    Dim strRecordset As String
    Dim dtstartdate As Date
    Dim dtenddate As Date
    strRecordset = "Select * from tempProcessPaymnetsSFSAmount"
    Set db = CurrentDb
    Set rstTmpProcessor = db.OpenRecordset(strRecordset, dbOpenDynaset)
    dtstartdate = [Forms]![modal_getdaterange]![textDateStart]
    dtenddate = [Forms]![modal_getdaterange]![textDateEnd]

    Set qdfPassThrough = db.CreateQueryDef("djtempPassThroughQuery")
    qdfPassThrough.sql = "SELECT sum(transactions.TransactionAmount)as batchamount , processors.processorName, " _
    & "transactions.ProcessedOn " _
    & "FROM processors, transactions " _
    & "where processors.id = 87 and " _
    & "transactions.transactionType_id = 3 " _
    & "AND transactions.ProcessedOn >= " & "'" & dtstartdate & "'" & " AND " _
    & "transactions.ProcessedOn <= " & "'" & dtenddate & "'" & " " _
    & "group by transactions.ProcessedOn ,processors.processorName"
    qdfPassThrough.ReturnsRecords = True
    Set rst = qdfPassThrough.OpenRecordset(dbOpenSnapshot) crashes here with data type mismatch
    x = 0
    Dale Houston, TX

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Please!! Setting a parameter in a Passthrough query

    this works as a saveed passthrough query
    select MAX(processorName) as processorName, SUM(TransactionAmount) as batchAmount, processors.id, processedon
    from processors, transactions
    where processors.id = (SELECT TOP 1 processor_id from merchant_processors
    where merchant_processors.merchant_id = transactions.MerchantID ORDER BY merchant_processors.id DESC) and transactions.transactionType_id = 2
    group by processors.id, ProcessedOn order by processorName


    This does not because I am missing something on the parameter

    @Param1 as datetime = forms!modal_getdaterange!textdatestart
    @Param2 as datetime = forms!modal_getdaterange!textdateend

    select MAX(processorName) as processorName, SUM(TransactionAmount) as batchAmount, processors.id, processedon
    from processors, transactions where
    processors.id = (SELECT TOP 1 processor_id from merchant_processors where merchant_processors.merchant_id = transactions.MerchantID ORDER BY merchant_processors.id DESC) and
    transactions.transactionType_id = 2 and ProcessedOn >= ' & @param1 & ' and ProcessedOn <= ' & @param2 & '
    group by processors.id, ProcessedOn order by processorName
    Dale Houston, TX

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    all seem to be the same/similar problem
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •