Access 2007 VBA: Returning data from SQL statements that use multiple variables

Question

I am working on a form that is meant to analyze financial data ahead of an insurance policy renewal. It needs to pull various premium and claim $$ totals from the tables, and insert them into the form. From there, it will run some calculations with them, but that should be the easy part. Where I'm struggling is the SQL statements to get the data, in the first place.

After narrowing it down a bit, I've found that the problem is the code is putting the SQL statement into the field on the form, instead of the answer the SQL statement should be providing. I have tried multiple things I've seen online, and can't figure out how to resolve this.

One of the SQL statements reads like this:

L12W = "SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem " _
 & " WHERE tblActPrem.EntID = '" & Me.ctlActEntID & "' " _
 & " AND tblActPrem.PolNum = '" & Me.ctltblRnwlTrack_PolNum & "'" _
 & " AND tblActPrem.APDate BETWEEN #" & L12M & "# AND #" & Me.ctlRnwAnalysisDt & "#;"""

It should be totalling premium data from the table, where the policy number and account number match what's on the form, and between the selected dates, and putting that total into the field on the form. Instead, I get this on the form:

SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit FROM tblActPrem  WHERE tblActPrem.EntID = '1235'  AND tblActPrem.PolNum = 'Policy1' AND tblActPrem.APDate BETWEEN #1/1/2014# AND #1/1/2015#;"

That is exactly how the statement should be running, but can anyone tell me how to make the leap from the statement, to the data?

Thank you!

Please post more code, especially the context of this string-concatenation. Do you have any other VBA code in your project which is successfully querying the database? — Dai
– Dai, Commented Jan 23, 2015 at 19:51

Parfait · Accepted Answer · 2015-01-24 00:31:36Z

Consider using DLookup or DSum in the control source of the form's field.

DLookup Solution

First, change your VBA SQL query into a stored SQL Query (notice quotes and # symbols are not necessary when referencing form controls):

SELECT Sum(tblActPrem.APWrit) AS SumOfAPWrit 
FROM tblActPrem 
WHERE tblActPrem.EntID = Forms!<yourformname>!ctlActEntID
AND tblActPrem.PolNum = Forms!<yourformname>!ctltblRnwlTrack_PolNum
AND tblActPrem.APDate BETWEEN Forms!<yourformname>!L12M 
AND Forms!<yourformname>!ctlRnwAnalysisDt

And then in the form field textbox's control source use the DLookUp function (no criteria argument is needed since this should return only one value).

= DLookUp("SumOfAPWrit", "<yournewqueryname>")

DSum Solution

Alternatively, you can change the entire SQL statement into a DSum but notice how long the criteria (WHERE statement) would have to be.

= DSum("APWrit", "tblActPrem", "EntID = Forms!<yourformname>!ctlActEntID
AND PolNum = Forms!<yourformname>!ctltblRnwlTrack_PolNum
AND APDate BETWEEN Forms!<yourformname>!L12M AND Forms!<yourformname>!ctlRnwAnalysisDt")

Collectives™ on Stack Overflow

Access 2007 VBA: Returning data from SQL statements that use multiple variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related