CODEWITHSUNDEEP
excelms-accessvba
marv
marv

Reputation: 1088

VBA obtaining a SUM of values from MS Access, returns error: No Value Given For One or More Parameter

I have the following code to retrieve a sum out of an access field. keep getting the error "No Value Given For One or More Parameter". I'm assuming its something simple. I tried looking up the syntax and google appears to give me what i already have. Appreciate your help.

EDIT

Public Sub sum()
Dim cn As Object
Dim rs As Object
Dim strSql As String
Dim strConnection As String
Dim countfrmdb As String
Dim currentmth As String
currentmth = Sheets("Data").Range("f3")  'this has been formatted to get the month name. e.g: Jan. Thats how its in my database.

Set cn = CreateObject("ADODB.Connection")
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=data.mdb"
strSql = "SELECT SUM(field_name) As Total FROM Table_name WHERE field_month_name = " & currentmth

cn.Open strConnection
Set rs = cn.Execute(strSql)
countfrmdb = rs.Fields(0)
MsgBox (countfrmdb)

Upvotes: 2

Views: 2219

Answers (2)

HansUp
HansUp

Reputation: 97101

You indicated currentmth is a text value containing the current month. So when you add currentmth to the WHERE clause without enclosing it in quotes, the db engine thinks Jan is the name of a parameter instead of a text value.

Change this ...

WHERE field_month_name = " & currentmth

to this ...

WHERE field_month_name = '" & currentmth & "'"

A good way to troubleshoot these type of issues is to examine the actual text of the SQL statement you're asking Access to execute. Debug.Print is useful for that purpose.

strSql = "SELECT SUM(field_name) As Total FROM Table_name WHERE field_month_name = " & currentmth
Debug.Print strSql

You can then view the output from Debug.Print in the Immediate window. And you can copy the statement text from there and paste it into SQL View of a new Access query for testing. Or you can show us the actual statement text which is producing the error your reported.

Upvotes: 2

Newd
Newd

Reputation: 2185

Based off of the information found here: No value given for the required parameter I would say that your Field_Name or table_name aren't correctly spelled. You can test this by purposefully putting in the wrong field name and you will get the same error you are getting.

Upvotes: 1

Related Questions