Too few parameters error in VBA query

Posted on 2009-02-08
Last Modified: 2013-11-07
I get the error message: "Too few parameters. Expected 1" when I run the following VBA code.  When I print tdate in the immediate window the query string is: SELECT Account, Symbol FROM [Fidelity Export] WHERE ([Fidelity Export].[Trans Date] > #01/20/09#)

What am I doing wrong?

Function mcrMakeT1()

On Error GoTo mcrMakeT1_Err

    Dim dst As DAO.Database, rstA As DAO.Recordset

    Dim tDate As String, tSQL As String, vI As Long


    With CurrentDb

        tDate = Format(.OpenRecordset("qryLastTransDateInHistory")![maxOfTransDate], "mm/dd/yy")


'        tSQL = "SELECT [Fidelity Export].ID, [Fidelity Export].Account, [Fidelity Export].[Security Description], " _

'            & "[Fidelity Export].Symbol,  [Fidelity Export].[Trans  Date], [Fidelity Export].[Transaction Description], " _

'            & "[Fidelity Export].Quantity , [Fidelity Export].Amount, [Fidelity Export].Price, [Fidelity Export].Comm, " _

'            & "[Fidelity Export].Fees, [Fidelity Export].[Accrued Interest], [Fidelity Export].[Settlement Date] " _

'            & "FROM [Fidelity Export] " _

'            & "WHERE [Fidelity Export].[Trans Date] > " & tDate


        tSQL = "SELECT Account, Symbol FROM [Fidelity Export] WHERE ([Fidelity Export].[Trans Date] > #" & tDate & "#)"

        Set rstA = .OpenRecordset(tSQL)


    End With


    Exit Function


    MsgBox Error$

    Resume mcrMakeT1_Exit


End Function

Open in new window

Question by:jknepper
    LVL 44

    Expert Comment

    Are you sure a date is returned from the query?
    LVL 75

    Accepted Solution

    Also,  check to be SURE all of the field and table names are spelled correctly, ie no typos.


    Author Comment

    From the Immediate window:

    print tdate
    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    If you take the SQL and convert it to SQL you can run in the query designer, do you get a prompt ?

    LVL 18

    Expert Comment

    The recordset won't like the query parameter. Save the date in a function and use the function in the WHERE.; WHERE([Fidelity Export].[Trans Date] > GetMyDate()).

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now