Question SUMIF in access (1 Viewer)

Hi All,

Looking for some help, I am creating a database to project a budget for my company. So far I have a table which contains a list of properties and the year a kitchen, bathroom, boiler etc was installed. As well as a life cycle on those components, for example a bathroom installed in 2005 would be due for replacement 2019 as well as a budgeted cost.

I created a form to show each component in a TXTbox as per attachment. what I want the form to do, when i type in a year in the box, the kitchen, bathroom and so on pre-populate as per the year typed in the box to project the cost for that year, so if I type in 2019 it would show forecasted cost. In the same way you would use the sumif on excel. The data is saved on a table so the expression would look up the values from there.

Although when I tried using =Sum(IIf... it got an Error not sure what I have done wrong or if it is even possible.

Any help would greatly appreciated.

Although when I tried using =Sum(IIf... it got an Error not sure what I have done wrong or if it is even possible.

Click to expand...

suggest provide the code you used and the error description since in principle that should work

Hi CJ,

thanks for the reply, the code i used which may be wrong was

=Sum(IIf([TblComponents]![Kitchen]="[YB]",[TblComponents]![Kitchen Cost],0))

YB relates to the name of the year box where the data would be entered, which I am trying to look up, as the year may change I wouldn't want to fix this to a set year.

also the error is a simple #Error when i view the form once I have saved the code.

thanks

what was the error message?

if this is on a form and in the header of footer region you need to refer to the control names so suggest it should be

=Sum(IIf([txtKitchen]=[txtYB],[txtKitchen Cost],0))

I presume you have named your controls with a txt prefix as good practice

I have a table which contains a list of properties and the year a kitchen, bathroom, boiler etc...

[TblComponents]![Kitchen]="[YB]",[TblComponents]![Kitchen Cost]

Click to expand...

Pretty sure you haven't set up your tables properly. You should not have a field called "Kitchen" nor "Kitchen Cost" in your tables. When you do that you are storing data in field names. "Kitchen" is a data value which means it should appear in a field of a table, not in a field name of a table. Same for its associated cost.

I suggest you complete your relationship tool, expand it so we can see all relationships and fields, take a screenshot and post it back here. Either that or post a sample of your database.