If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Can I Build an Access Statement (in 2007) as a String and Execute
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 |
#2
|
|||
|
|||
Can I Build an Access Statement (in 2007) as a String and Execute
On Thu, 22 Apr 2010 19:21:01 -0700, Terry
wrote: 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 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 |
#3
|
|||
|
|||
Can I Build an Access Statement (in 2007) as a String and Execute
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. "Tom van Stiphout" wrote in message ... On Thu, 22 Apr 2010 19:21:01 -0700, Terry wrote: 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 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))") |
#4
|
|||
|
|||
Can I Build an Access Statement (in 2007) as a String and Execute
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) "Terry" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|