What is wrong with my expression?

[Al]

I am trying to have an automatic "dsum" for the year prior to a year that is
calculated in field called "Selectedyear" so if selectedyear = 2008 then I
want the dsum to calculate field "2007" in table "BuildingDetails".
BuildingDetails table has fields (2007,2008,2009,2010,2011,2012) I want to be
able to Dsum the year previous to whichever year calculated in the
"Selectedyear" textbox on a form.
the following expression gives me the result of:$120,420 (it is not correct
result)
DSum([Selectedyear]-1,"BuildingDetails","Area='Patient Care'").
If I right the expression, for example:
DSum("[2007]","BuildingDetails","Area='Patient Care'"), it works fine and
gives me the right sum ($16,885). The only problem with this is that the user
has to go to the expression and change the year according to what shows up in
the "Selectedyear" text box. is there a way to automate it the way I
described?
thanks
Al

 

[Douglas J. Steele]

Having fields named 2007, 2008 etc. is not a very good database design.
That's called a repeating group, and should be avoided, both because of the
difficulties it causes, plus the fact that you're essentially hiding
information in the name of the field.

Instead of 6 fields on 1 row, you should have 6 separate rows in the table,
one for each year.