Prev: search command
Next: Finding Duplicates with many fields
From: Terry on 12 Apr 2010 16:38

Thanks for the great looking VBA code.
I've not done VBA yet, but it was in the list of To Learns. I think it just
moved up in priority. Where does a VBA subroutine like this get placed in
the application? Is it a control event?

"Tom Wickerath" wrote:

> Hi Terry,
>
> Could you not determine the two values first, add them together, and then
> run a single update query? Something like this, in a VBA subroutine:
>
> Dim strSQL As String
> Dim lngCount1 As Long
> Dim lngCount2 As Long
> Dim lngTotal As Long
>
> lngCount1 = _
> DCount("AuditDtlCnt","AuditDetailInitialEval","(((AuditDetailInitialEval.Medicare)=On)
> AND ((AuditDetailInitialEval.DateofService) Between #12/31/2008# And
> #1/1/2010#) AND ((AuditDetailInitialEval.ReasonableAndNecSvc)=Off))")
>
> lngCount2 = _
> DCount("AuditDtlCnt", "AuditDetailTreatmentTbl",
> "(((AuditDetailTreatmentTbl.Medicare)=On) AND
> ((AuditDetailTreatmentTbl.DtOfSvc) Between #12/31/2008# And #1/1/2010#) AND
> ((AuditDetailTreatmentTbl.SvcReasonableNecessary)=Off))")
>
> lngTotal = lngCount1 + lngCount2
>
> strSQL = "UPDATE AuditRptTbl " _
> & "SET AuditRptTbl.AuditRptNoCount = " & lngTotal & " " _
> & "WHERE AuditRptTbl.AuditRptID=22;"
>
> 'Note: You'll likely want to substitute a variable for the hardcoded 22,
> above.
>
> CurrentDB.Execute strSQL, dbFailOnError
>
> 'Add appropriate error handling code
>
>
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> __________________________________________
>
> "Terry" wrote:
>
> > I have a row in a table that was populated with the 1st query below. I'd
> > like to update the existing numeric value in that table with a 2nd query that
> > uses DCount from another table. Is that possible? If it is, what would the
> > query look like?
> > Thanks,
> > TerryoMSN
> >
> > 1st Query That Populates the Existing Numeric Value
> > UPDATE AuditRptTbl SET AuditRptTbl.AuditRptNoCount =
> > DCount("AuditDtlCnt","AuditDetailInitialEval","(((AuditDetailInitialEval.Medicare)=On)
> > AND ((AuditDetailInitialEval.DateofService) Between #12/31/2008# And
> > #1/1/2010#) AND ((AuditDetailInitialEval.ReasonableAndNecSvc)=Off))")
> > WHERE AuditRptTbl.AuditRptID=22;
> >
> > 2nd Query That DCounts Value From Second Table That Would be Added to
> > Existing Value
> > UPDATE AuditRptTbl SET AuditRptTbl.AuditRptNoCount =
> > DCount("AuditDtlCnt","AuditDetailTreatmentTbl","(((AuditDetailTreatmentTbl.Medicare)=On)
> > AND ((AuditDetailTreatmentTbl.DtOfSvc) Between #12/31/2008# And #1/1/2010#)
> > AND ((AuditDetailTreatmentTbl.SvcReasonableNecessary)=Off))")
> > WHERE AuditRptTbl.AuditRptID=22;
 | 
Pages: 1
Prev: search command
Next: Finding Duplicates with many fields