countif access alternative

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Access Training and help » Countif access alternative

Countif access alternative

resolvedResolved · Medium Priority · Version 2016

stuart has attended:
Access VBA course

Countif access alternative

hi

i need to be able to use a countif statement similar to excel

so in a continuous form, each line

Tracker_ID|MCUDate|vehicleAtFault|SubForm|failureLevel(calculation Needed)
1|12/08/18|56|coupler|1
2|13/08/18|45|ATO|1
3|14/08/18|56|coupler|2
4|15/09/18|56|coupler|1


so it needs to look at the VehicleAtFault and SubForm, in a 14 day period, so as you can see the failure level on id "3", is "2" as there are two records in a 14 day period.


i have attempted to use Dcount for this, but it didnt work for a continuous form

RE: countif access alternative

Hi Stuart

Apologies for the delay, our Access VBA trainer has been in training and will be in contact with you shortly

Kind regards
Wendy Canelas
Microsoft Office Trainer

RE: countif access alternative

Hi Stuart,

I have the last three days tried to get a solution. I have tried VBA, I have tried the Dcount as you have. I have tried to find a SQL solution, but unfortunately I have had no luck.

The closest I could find is the link below.

https://community.spiceworks.com/topic/1971833-ms-access-question-how-do-you-get-a-count-of-records-that-have-time-belonging


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

RE: countif access alternative

Hi Jen's

Thanks for looking, as all of the data is stored in one table, is it possible to push the data to an Excel sheets and do the calculation there, then pull the answer back?

RE: countif access alternative

Hi Stuart,

I do not think that's a good solution. There must be a way in Access.

Is it possible for you to send me a copy of the database.

forum@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

RE: countif access alternative

Hi Jen's

I've sent you the database, but it's split so you'll have to relink the tables to the front end

RE: countif access alternative

Hi Jen's

I've sent you the database, but it's split so you'll have to relink the tables to the front end

RE: 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

RE: countif access alternative

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

Attached files...

sumproduct.xlsx


 

Access tip:

Space marks

It is good practice not i to have space marks for field names as this can lead to problems when using queries or VBA code. It is much better to use an underscore charcter to represent spaces in field names

View all Access hints and tips


Server loaded in 0.07 secs.