?
Solved

Too few parameters error in VBA query

Posted on 2009-02-08
5
Medium Priority
?
638 Views
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?

Thanks!
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
 
mcrMakeT1_Exit:
    Exit Function
 
mcrMakeT1_Err:
    MsgBox Error$
    Resume mcrMakeT1_Exit
    
 
End Function

Open in new window

0
Comment
Question by:jknepper
5 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 23587142
Are you sure a date is returned from the query?
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 200 total points
ID: 23587152
Also,  check to be SURE all of the field and table names are spelled correctly, ie no typos.

mx
0
 

Author Comment

by:jknepper
ID: 23587156
From the Immediate window:

print tdate
01/20/09
0
 
LVL 75
ID: 23587174
If you take the SQL and convert it to SQL you can run in the query designer, do you get a prompt ?

mx
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 23587190
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()).
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

850 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