Can I Build an Access Statement (in 2007) as a String and Execute

[Terry]

Hi,
I know that I can build a SQL statement within a string and then execute it
like the following:
strSQL = "UPDATE tblAuditRpt " _
& "SET tblAuditRpt.AuditRptYesCount = " &
lngInitialEvalCount & " " _
& "WHERE tblAuditRpt.AuditRptID=25;"
CurrentDb.Execute strSQL, dbFailOnError

Can I do the same with an Access statement itself? For example, How could I
execute the following statement if I built it in a string?

lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval",
"(((AuditDetailInitialEval.Medicare)=On) AND
((AuditDetailInitialEval.DateofService) Between tblAuditDateRange.StartDate
And tblAuditDateRange.EndDate))")
Thanks,
Terryomsn

 

[Tom van Stiphout]

On Thu, 22 Apr 2010 19:21:01 -0700, Terry

Ironically: check out the Eval function in the Help file.
Debug.Print Eval("2+3")
5
Not sure you can use it for assignments though. Since you have Option
Explicit on per best practices, and lngInitialEvalCount is potentially
an undeclared variable, this likely will not work. But it may be a
step in the right direction nevertheless.

-Tom.
Microsoft Access MVP

 

[Paul Shapiro]

Looking at the original example, it's not clear why you would need to build
the actual code as a string. Only the DCount arguments would normally need
to be set by code.

Instead of creating this code as a string:
lngInitialEvalCount = DCount ("AuditDtlCnt", "AuditDetailInitialEval",
"(((AuditDetailInitialEval.Medicare)=On) AND
((AuditDetailInitialEval.DateofService) Between
tblAuditDateRange.StartDate
And tblAuditDateRange.EndDate))")

I would think the code could be written as:
strOutputExpression = "AuditDtlCnt"
strDataSource = "AuditDetailInitialEval"
strWhere = _
"AuditDetailInitialEval.Medicare = True _
AND AuditDetailInitialEval.DateofService
Between tblAuditDateRange.StartDate And tblAuditDateRange.EndDate"
lngInitialEvalCount = DCount (strOutputExpression, strDataSource, strWhere)

Your code can set the string variables with whatever dynamic conditions
would apply before calling DCount.

You could also get a dynamic row count by using sql instead of DCount:
strSQL = "Select count(*) From MyTable Where ..."
and then executing the sql to get the output.

As an aside, I don't think the code as written would work. The Where clause
includes conditions using columns from tblAuditDateRange, which is not part
of the data source. If your conditions are located in form controls, you
would concatenate those values into the where-condition string you build.

 

[david]

execute the following statement if I built it in a string?

You would write it into a function in code module,
then call eval or run or runcode or (several other
options).

Writing code into a code module is one of those
things that sort of exists for backward compatibility,
but gets flakier and more difficult every version.

So the more generally correct answer is, don't do it that way.

In the small sense, the problem with your code is
that "lngInitialEvalCount" should be an object or
a property, not a VBA variable.

In the large sense, the problem with your code is
that if you need to evaluate that expression, you
have chosen the wrong approach,

(david)