Sumiif Function in Report's Text Box's Control Source Property

[JithuAccess]

Hello,

I have three fields in my Table: ID, Status and Amount. I want to find the sum of the Amount if the Status is "Approved" in my report. What would be the Syntax if I am using the "Sumiif" Function?

I have put the following Function in the Text Box's control source property of my Report

=Sum([Amount](IIf([Status]="Approved",0)))

And I am getting an Error message like "Undefined function 'Amount' in Expression". Could you please help me how to solve this.

Thanks

 

[theDBguy]

Maybe try something like:

=Sum(IIf([Status]="Approved",[Amount],0))

 

[JithuAccess]

Maybe try something like:

=Sum(IIf([Status]="Approved",[Amount],0))

Thanks a lot for your reply. But unfortunately, it is showing as $0.00.


Thanks

 

[theDBguy]

Thanks a lot for your reply. But unfortunately, it is showing as $0.00.


Thanks

What is the data type of the field [Status]?

 

[JithuAccess]

What is the data type of the field [Status]?

Short Text

 

[theDBguy]

Short Text

Thanks. Where did you put the Textbox (which section of the report)?

 

[JithuAccess]

Thanks. Where did you put the Textbox (which section of the report)?

Report Header

 

[theDBguy]

Report Header

For now, try moving it to the Details section and see what happens.

 

[JithuAccess]

For now, try moving it to the Details section and see what happens.

Sure. I will do it

Thanks

 

[theDBguy]

Sure. I will do it

Thanks

Let us know what happens...

 

[arnelgp]

you can also use DSum() function as Control Source of the textbox:

=DSum("Amount", "yourTableName", "Status = 'Approved'")

 

[JithuAccess]

Let us know what happens...

I guess I solved it. Thanks a lot for your Help.

This is my Table:

This is my Report:

And this is the Result:

Thanks a lot for your help. And sorry for the late reply.

Thanks

 

[JithuAccess]

you can also use DSum() function as Control Source of the textbox:

=DSum("Amount", "yourTableName", "Status = 'Approved'")

Thanks a lot for your suggestion. It works perfect.



This is my Table:

View attachment 86897

This is my Report:

View attachment 86898

And this is the Result:

View attachment 86899

Thanks a lot for your help.

Thanks

 

[theDBguy]

I guess I solved it. Thanks a lot for your Help.

This is my Table:

View attachment 86897

This is my Report:

View attachment 86898

And this is the Result:

View attachment 86899

Thanks a lot for your help. And sorry for the late reply.

Thanks

Hi. Glad to hear you got it to work. Good luck with your project.

 

[JithuAccess]

Hi. Glad to hear you got it to work. Good luck with your project.

Thanks a lot. It saved lot of time and effort. I used to create a query to find the sum and then create a sub report to bring that result in to my main Report.

Thanks