CODEWITHSUNDEEP
vbams-access
user3323922
user3323922

Reputation: 205

VBA Query returning nulls

Using the query builder in Access I am able to find the total, but I need to find the total using the vba code builder. The code given here gives me a null value. 

Dim rst As DAO.Recordset
Dim dbs As Database
Dim strSQL As String

Set dbs = CurrentDb


strSQL = "SELECT Sum(GiftRcvd.Rcvdamount) AS SumOfRcvdamount FROM OurEvents INNER JOIN GiftRcvd ON OurEvents.EventName = GiftRcvd.EventName " & _
"WHERE ((([OurEvents].[EventDate])>" & Me.DateFrom.Value & " And ([OurEvents]![EventDate])< " & Me.DateTo.Value & "));"

Set rst = dbs.OpenRecordset(strSQL)

SumOfRcvdamount = rst![SumOfRcvdamount]

MsgBox SumOfRcvdamount

Upvotes: 0

Views: 67

Answers (2)

Ann L.
Ann L.

Reputation: 13965

It's likely that your query is returning an empty recordset. Assuming you have data, this most likely means that your HAVING clause is filtering out the records you want.

As I remember, date literals in Access have to be in the format #1/30/2019#: a clause in the form [EventDate] > 1/30/2019 will not evaluate the way you want.

So try bracketing those date parameters with #:

[OurEvents].[EventDate])> "#" & Me.DateFrom.Value & "#"

Strictly speaking, you should avoid assembling queries from strings (due to the possibility of SQL Injection attacks): you should instead parameterize them and pass parameter values. BUT, that's harder to do in Access than in other forms of SQL.

Upvotes: 1

Gustav
Gustav

Reputation: 55806

You have to format your date values to valid string expressions:

"WHERE ((([OurEvents].[EventDate])> #" & Format(Me.DateFrom.Value, "yyyy\/mm\/dd") & "# And ([OurEvents]![EventDate])< #" & Format(Me.DateTo.Value, "yyyy\/mm\/dd") & "#));"

Upvotes: 0

Related Questions