Cross column addition query

[smytelka]

BACCT	BCC	BYEAR	BFINCD	BFWD	BJAN	BFEB	BMAR	BAPR	BMAY	BJUN	BJUL	BAUG	BSEP	BOCT	BNOV	BDEC
1000		2012		100	5	5	2	2000	3				2
1200		2011		50	7	22			-2073364.1
1300		2012								-400		400

I have a table that has balance forwards for accounts in an accounting program and then has the month by month changes in balances to those accounts. I need to create a query that ask the users for a month and year and returns two columns: Account and amount. It would take the month column and add the BFWD column plus all the months until the given month (returning 05 would add BFWD and BJAN thru BMAY). It needs to be a single query. The problems I am having is the code I drew up is too long to fit in the Field box. It is basically a very long nested iif and I put the (IIf([BFWD] Is Null,0,[BFWD])) in there since when fields were blank it would return Null(this portion of the query I changed to brown). The other problem is that I need the user to enter the month but I do not want it to show up in the results but if I remove the checkmark underneath the box it doesn't work.

Any help is greatly appreciated.

SELECT GBFWD.BACCT AS Account,
 IIf([Month]=1,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN])),IIf([Month]=2,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB])),IIf([Month]=3,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR])),IIf([Month]=4,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR])),IIf([Month]=5,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY])),IIf([Month]=6,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN])),IIf([Month]=7,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL])),IIf([Month]=8,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL]))+(IIf([BAUG] Is Null,0,[BAUG])),IIf([Month]=9,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL]))+(IIf([BAUG] Is Null,0,[BAUG]))+(IIf([BSEP] Is Null,0,[BSEP])),IIf([Month]=10,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL]))+(IIf([BAUG] Is Null,0,[BAUG]))+(IIf([BSEP] Is Null,0,[BSEP]))+(IIf([BOCT] Is Null,0,[BOCT])),IIf([Month]=11,(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL]))+(IIf([BAUG] Is Null,0,[BAUG]))+(IIf([BSEP] Is Null,0,[BSEP]))+(IIf([BOCT] Is Null,0,[BOCT]))+(IIf([BNOV] Is Null,0,[BNOV])),(IIf([BFWD] Is Null,0,[BFWD]))+(IIf([BJAN] Is Null,0,[BJAN]))+(IIf([BFEB] Is Null,0,[BFEB]))+(IIf([BMAR] Is Null,0,[BMAR]))+(IIf([BAPR] Is Null,0,[BAPR]))+(IIf([BMAY] Is Null,0,[BMAY]))+(IIf([BJUN] Is Null,0,[BJUN]))+(IIf([BJUL] Is Null,0,[BJUL]))+(IIf([BAUG] Is Null,0,[BAUG]))+(IIf([BSEP] Is Null,0,[BSEP]))+(IIf([BOCT] Is Null,0,[BOCT]))+(IIf([BNOV] Is Null,0,[BNOV]))+(IIf([BDEC] Is Null,0,[BDEC]))))))))))))) AS Amount,
 GBFWD.BYEAR, [Enter Month MM] AS Month
FROM GBFWD
WHERE (((GBFWD.BYEAR)=[Enter Year of Report YYYY]))
ORDER BY GBFWD.BACCT;

[June7]

Part of the issue is this data is not normalized structure.

This could require a custom VBA function that can be called from query or textbox. The function could open a recordset of the relevent record to calculate the Amount and return the result to the calling object.

[smytelka]

I'm not that advanced however maybe you could tell me how to make an expression without using a field. For example if I want to make an expression like this:
Amount: [Field1]+[Field2]
And only Field1 is a Field in my table and I want to create Field2 like this:
Field2: (5*3)
But I don't want Field2 to show when I run the query I just want Amount column how would I do that?

[June7]

Maybe:

Amount: [Field1] + 5 * 3