Taking an indivual value from a multivalue field???

  • Thread starter Thread starter Access_Beginner
  • Start date Start date
A

Access_Beginner

I’ve run into a problem though while designing a report and I was hoping you
could help me out. I’ll try to explain it as clear as I can.
I have three different accounts that all have transactions of revenues and
expenses. I wanted to sort the data from the account for the last two weeks,
so I made the form based on a query that has a date criteria on the last two
weeks. The only grouping I did for the report was by accounts so I can see
all the revenues and expenses in each. I added sums of the revenue and
expenses to the account footer, so I could have it generate the sums of
revenues and expenses for each account. My problem arises after this. I want
to pull the calculations from these sums of revenue and expense for each
account. Let’s say the accounts are named 1, 2, and 3. I would like to take
the sum of revenue from account 2, then divide that by two, then add it to
another number. Basically, I would like to make a formula with the sums of
revenues from each account.

I only want to the sum of revenues from one account, not all so i could plug
that value into a formula
 
In a text box in your report type
=(Sum(IIF([AccountNumber]=2,[Revenue],0)/2) + AnotherNumber

Translation: If AccountNumber=2 Then Sum the Revenue for that Account
Number, count the revenue of other account numbers as 0. Divide the result
of this by 2. Add another Number to this latter result.



Evi
 
You are my hero!
i seriously worked on it the whole day yesterday trying to figure it out
myself and then finally gave up at around 2 in morning. cant believe i never
tried that IF statement...
Thanks again Evi
 
Back
Top