Countif access alternative

Hi Stuart,

I am sorry, but we are changing our URL and for some reason your database didn't arrive.

Can I please ask you to send it to:

info@stl-training.co.uk




Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Hi Stuart,

I am close but still far away.

I have done a try calling a SumProduct function from Excel. I have a problem with a variable.

I am running out of time this week, so you may see where I go wrong in the function.

I have used your table "Table_ENG_MCU_VehicleWash_M2M" as example.

I have attached a Excel worksheet so you can see my logic.

Function AddCountif(WashDate As Variant, VehicleID As Variant)

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("Table_ENG_MCU_VehicleWash_M2M", dbOpenTable)

Dim iCount As Integer

Set xl = CreateObject("Excel.Application")
iCount = xl.WorksheetFunction.SumProduct((rs("Vehicle_IDFK") = " & VehicleID & ") * (rs("Washdate") - " & WashDate & " <= 14) * (rs("Washdate") - " & WashDate & " >= -14))

AddCountif = iCount


rs.Close
Set xl = Nothing


End Function



Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector