Dcount in Query

[matthewnsarah07]

Hi

I am currently adding a piece of code which will loop through a recordset (query) to send out multiple emails.

I am wanting to shows some totals within the email itself which are a count of fields bearing each email address. Is it possible to use Dcount or similar in the query itself or do I need to use another method?

Thanks for your help

 

[DCrake]

If you write a function in a module that gets the data you require you can call this in your function.

Lets say you want to sum all the records in your table that have a start date earlier than the current month you are grouping on.

First create your Function

Public Function GetRunningSum(AnyDate As Date) As Long

    GetRunningSum = Nz(DCount("*","YourTable","StartDate<#" & AnyDate & "#"),0)

End Function

Next in you query add a column and enter

RunningSum:GetRunningSum([StartDate])

As you can see the startdate is passed to the function wich is permutated and then the result is passed back to the query.

David