Count record with two criteria in unbound textbox-CodePudding

i have fields named "Date of Payment" and "Type of Application". i have also unbound textbox with a name "txtCount". i want to display in "txtCount" the total count of records if "Date of Payment" = year 2022 AND "Type of Application" = "New Transaction". The year 2022 is in another table field name "Calendar Year" and the year 2022 is in text159 using: =DLookUp("CalendarYear","tblControlNumber","ControlID")
this is my current formula in my unbound textbox and i am getting error.
=Sum(IIf([Type of Application]="New Transaction" And Year([Date of Payment])=[Text159.Value],1,0))

can anyone help pls, thank you

i want to get Number of New transaction in year 2022 only. and if i update my calendar year 2023, i can also get the number of new transactions in year 2023.

CodePudding user response：

The DLookup() is not complete - WHERE CONDITION needs parameter.

=DLookUp("CalendarYear","tblControlNumber","ControlID=" & [somefield or textbox])

CodePudding user response：

as June 7th pointed out Text159 isn't set up properly in this example the equivalent txtYear just has 2022 typed in. Assuming:

-------------------------------------------------------------------------------------
|    SomeTableID     |   DateofPayment    | TypeofApplication  |    CalendarYear    |
-------------------------------------------------------------------------------------
|                  1 |         11/24/2022 | New Transaction    |               2022 |
-------------------------------------------------------------------------------------
|                  2 |         10/24/2022 | New Transaction    |               2022 |
-------------------------------------------------------------------------------------
|                  3 |          1/22/2021 | Old Transaction    |               2021 |
-------------------------------------------------------------------------------------
|                  4 |          2/23/2021 | Old Transaction    |               2021 |
-------------------------------------------------------------------------------------

txtCount=DCount("SomeTableID","SomeTable","Year(DateofPayment) = " & [txtYear])

txtCountJustRecord= =IIf([TypeofApplication]="New Transaction",1,0)

Your issue is most likely that the iff function is referring to just the selected record of the forms record source. Instead use something like txtCount