CODEWITHSUNDEEP
sqlms-accessvbaparameterized
147
147

Reputation: 592

Running a parameter query in Access VBA

I am trying to run the following query in a VBA function. I keep getting "Too few parameters. Expected 1."

strSQL = "Parameters [Report Date] DateTime;" & vbCrLf & _
                    "SELECT SCF.code AS [Stock Code], " & vbCrLf & _
                    "SCF.desc AS [Description], " & vbCrLf & _
                    "SCF.grp AS [Product Group]," & vbCrLf & _
                    "SCF.qCurr AS [Closing Stock], " & vbCrLf & _
                    "SCF.abp AS [Avg Price], " & vbCrLf & _
                    "Sum(([Closing Stock]*[Avg Price])) AS [STOCK VALUE], " & vbCrLf & _
                    "MaxDate.tDate AS [Last Transaction Date], " & vbCrLf & _
                    "Sum(IIf(([Last Transaction Date]>[Report Date]),([Closing Stock]*[Avg Price]),0)) AS [After Report Date], " & vbCrLf & _
                    "DateDiff(""d"",[Last Transaction Date],[Report Date]) AS [Days since Last Transaction], " & vbCrLf & _
                    "[Report Date]" & vbCrLf & _
            "INTO [FinReport] " & vbCrLf & _
            "FROM SCF RIGHT JOIN MaxDate ON MaxDate.parent = SCF.this "
strSQL = strSQL & _
            "WHERE (SCF.qCurr <> 0) " & vbCrLf & _
            "GROUP BY SCF.code, " & vbCrLf & _
                        "SCF.desc, " & vbCrLf & _
                        "SCF.grp, " & vbCrLf & _
                        "SCF.qCurr, " & vbCrLf & _
                        "SCF.abp, " & vbCrLf & _
                        "MaxDate.tDate" & vbCrLf & _
            "ORDER BY MaxDate.tDate;"

Set qdf = db.CreateQueryDef("", strSQL)
qdf.Parameters("[Report Date]").Value = Form_IO_Form.ReportDate_TB.Value
qdf.Execute

I have verified that all fields (other than [Report Date] of course) exist and the query runs by itself as an access query (pop up asks for [Report Date]).

Help!

Edit 1: As requested here is the DB file as a ZIP. It is an Access 2007 .accdb file
DB File

Upvotes: 1

Views: 6661

Answers (2)

HansUp
HansUp

Reputation: 97131

Your SQL statement will trigger error #3122 from the db engine:

You tried to execute a query that does not include the specified expression 'DateDiff("d",[Last Transaction Date],[Report Date])' as part of an aggregate function.

That error will cause the statement to fail before the db engine even considers any parameters.

When you build a SQL statement with VBA, it's better to start with one the db engine will accept. Then you should also follow the sound advice from @mwolfe02 to Debug.Print strSQL ... to give yourself an opportunity to examine the completed statement you're asking the db engine to execute.

Edit: Having examined the ACCDB file you uploaded, I still don't understand why your query doesn't trigger error #3122. However the query does work as a saved query and can work when you execute it from VBA code. The reason you got the complaint about "too few parameters" is that you weren't actually executing the temporary QueryDef you created. Instead you were attempting to execute the SQL text like this:

' Execute created Query '
CurrentDb.Execute strSQL, dbFailOnError

If you change to this approach (as you indicated in your question), it works without error:

qdf.Execute

Upvotes: 1

mwolfe02
mwolfe02

Reputation: 24237

I am guessing that you have a typo in one of your field names. The easiest way to find it is to throw a Debug.Print strSQL line immediately before your Set qdf... line.

Then create a new query in the Access UI, switch to SQL view, paste in the SQL text from the immediate window, and execute the query. Access will prompt you for the Report Date (which you are expecting) and the mistyped name of one of your fields.

Upvotes: 1

Related Questions