Replicate formula of excel in access

[prateekjas]

Hi



I have 2 columns i.e. Period/Date (Col A) and code (Col B). It just check which code is present in previous month

=COUNTIFS(A:A,DATE(YEAR(A2),MONTH(A2)-1,1),B:B,B2)

How can i replicate same formula in access.

Thanks

[ranman256]

Dcount("*","query")
or
build a query that counts the dates range given. then Dcount will give the result.

[CJ_London]

access is not excel - would be better if you provided some example data and the result required. Plus where you want to use this - in a query? on a form or report? as a single value? or multiple rows?

you might use sql or perhaps as ranman suggests the dcount function

With regards your excel formula, how does it work if A2 is a date in January? Month would then be 0.

[apr pillai]

Some Excel Function User-Defined implementations in Microsoft Access:

1. Rounding Function MRound() of Excel.
2. Microsoft Excel Power in Access.
3. PROPER() Function of Excel in Access.
4. ROUNDUP() Function of Excel.
5. ROUNDDOWN() Function of Excel.

[ssanfu]

Welcome to the forum.....

Are the dates in "Period/Date" (Col A) below A2 ALWAYS going to be the first day of the month?
That is what the formula is looking for, no matter what date is entered into A2.



Given the dates and codes below, what would the count be?

	A	B
1
2	12/20/2019	Code3
3
4	11/5/2019	Code3
5	11/1/2019	Code3
6	11/24/2019	Code3
7	11/19/2019	Code3
8	11/1/2019	Code10

Based on your formula, I would think the count would be 1. Is that what you want?
Or should the count be 4??? (ANY day in the month and B2 = "Code3")



I would write a function (a UDF) to return the count.
Do you have a database started? Would you post a copy?