CODEWITHSUNDEEP
vbams-accessexceptionucanaccessdata-access
Kier Arts
Kier Arts

Reputation: 21

Count record with two criteria in unbound textbox

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.

Upvotes: 1

Views: 84

Answers (2)

mazoula
mazoula

Reputation: 1321

As June7 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 IIf() function is referring to just the selected record of the form's RecordSource. Instead use something like txtCount

enter image description here

enter image description here

Upvotes: 0

June7
June7

Reputation: 21379

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

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

Upvotes: 0

Related Questions