Populate Calculated field in query

[Mpike926]

Hi All,
Thanks for taking the time to read.
Basically what I have is a cashflow subform on a business plan form. In the subform, in the footer, there is a field that sums up all of the expenses and the income and subtracts to get the net income for that cashflow.
This is a calculated field. I have a report, where one field on it is Net Income of Business Plan. I dont know how to display this field on the subform into the access report. Any Help is appreciated.
Info:
The net income in the sfrm cashflow is stored in Text263. Its value is this
=(([totalprincipal]+[text256]+[text257]+[text175]+[text177]+[text179])-([text185]+[text187]+[text189]+[text191]+[text193]+[text195]+[text197]+[text199]+[text258]))

[June7]

Options:

1. do the same calculation in the report

2. report textbox ControlSource expression references the subform textbox
=Forms!formname!subformcontrolname.Form.Text263

3. pass the value to report with OpenArgs argument
DoCmd.OpenReport "report name", , , , Me.subformcontrolname.Form.Text263
then extract the value with expression in textbox ControlSource
=[OpenArgs]

[Mpike926]

thanks for answering. I tried the first two but they dont seem to work. When I do the Openargs, do i put the DoCmd into the report code, or in the form code?
Even more easily, cant I just populate a bound field in my table with the data from the calculated field? Like set its value to be equal?
I tried Me.Netincome=Me.Text263 but that didnt work :/

[June7]

Option1 would be an expression in report textbox that refers to other report textboxes or fields.

Option2 and 3 need to get the right syntax in referencing the subform control. Can be tricky.

How are you presently opening the report? Option3 is code to open the report so the OpenReport code would be behind form.

The Me reference won't work because Me works only to reference the form or report it is behind. Also, VBA cannot set the value of report textbox.

[Mpike926]

June,
Let me give you some more detail
We have porftolios that have many assets in each, (some with 10 assets, some with 100)
It is a simple report with the fields going across the top, lets say, Loan#-Borrower-Purchase Price-Current Balance etc. I need the calculated NetIncome(text263) to go across the top and display for each asset as well.

How are you presently opening the report? Option3 is code to open the report so the OpenReport code would be behind form.

It currently opens in print preview. We just view it like this and then send it to the printer to bring into meetings. Sometimes we also print it to PDF to send out to investors. I have attached a report showing what it looks like to better give you an idea. (I hid critical info)
The red box of Net Income is the field that text263 should produce

[June7]

By "How" I was asking by what method do you open the report - by code (macro or VBA) or manually from the navigation pane? I asked because you did not know where to put the DoCmd.OpenReport code.

I have no idea what fields are used in your calculation because you have not given your textboxes meaningful names. If those fields are on the report then build expression in textbox on report same as built in textbox on form. Or construct the expression in RecordSource query and refer to that constructed field in report textbox.

[Mpike926]

Oh, well by how, they choose a portfolio from a combo box, and click a button that runs a macro. The report has criteria in it for the portfolio name to match what is in the combo box

[June7]

Okay, so using macro, I use only VBA. I suppose macro can pass OpenArgs argument.

Did you try the calculation in query or report textbox? This will eliminate need for reference to the subform.

[Mpike926]

I have tried the calculation in the query and the report textbox. if I just enter in the =(etc+etc+etc)-(etc+etc+etc) when i open the report it asks me to enter the criteria for each text field

[June7]

So Access is not finding the names used in the expression and treating the terms as popup input prompts. Are these data fields in the report's RecordSource?

Sorry, misread this earlier question:

Even more easily, cant I just populate a bound field in my table with the data from the calculated field? Like set its value to be equal?
I tried Me.Netincome=Me.Text263 but that didnt work

Could but not advisable.

Want to provide db for analysis? Follow instructions at bottom of my post.

[Mpike926]

June,
Here is a copy of my database. I have removed everything from it except things that need to be in there to get it working. (I shrunk it from 64 megs down to a few hundred KB (lol)
Anyways so when you open it it will default open to the report form, where you will see a pipeline button. Users choose the portfolio from the combo box, click the button and then the report opens. (there is just one portfolio in there, choose that one)
Continuing on you will see forms labeled, Frm Cashflows Bplan (main form) and then frm cashflowdetailalternative and recommended (these are the subforms on the bplan main form)
If you open the Frm Cashflows Bplan you will see a net income field with a red box around it... That is the field I am trying to have displayed on the report. This field is linked to the subform frm cashflowdetailrecommended using =[recommended]![text263].
It doenst matter if the number comes directly from the subform or the main form itself, as they are the same thing. I hope I was clear enough in my explanation, and if you have any questions I can answer them for you.
Thanks for helping =]

[June7]

Is the MLS number unique in tblLoanGeneral?

See if this helps.

Build a query with join of tblLoanGeneral and tblCashFlow that summarizes tblCashFlow data grouping on the MLSNumber, I call it SumByMLS.

If MLSNumber is not the appropriate grouping field, maybe ID from tblLoanGeneral is.

Join that query to tblLoanGeneral in the report's RecordSource. Now the cash flow fields are available (as totals grouped by MLS or ID) for the NetIncome calculation.

[Rod]

Hi Both, I've joined!

Options:

1. do the same calculation in the report

2. report textbox ControlSource expression references the subform textbox
=Forms!formname!subformcontrolname.Form.Text263

3. pass the value to report with OpenArgs argument
DoCmd.OpenReport "report name", , , , Me.subformcontrolname.Form.Text263
then extract the value with expression in textbox ControlSource
=[OpenArgs]

Unfortunately 1 must be dismissed because, at present, the report query does not contain all the required fields.

Also 2 must be dismissed if, as I believe is being said, the form is not necessarily loaded at the time of report generation. Moreover the form is for a single loan id whereas the report seems to be continuous for all loan ids.

Last, 3 is not viable for much the same reasons.

Build a query with join of tblLoanGeneral and tblCashFlow that summarizes tblCashFlow data grouping on the MLSNumber, I call it SumByMLS.

If MLSNumber is not the appropriate grouping field, maybe ID from tblLoanGeneral is.

Join that query to tblLoanGeneral in the report's RecordSource. Now the cash flow fields are available (as totals grouped by MLS or ID) for the NetIncome calculation.

Good idea. I think ID (LoanID) is the PK for this. The link master/child values for the form are on ID/LoanID.

Here's some analysis I've just done for the notorious Text263 control. May help in building the query extension.

[Rod]

Forgot to mention in the last post that you don't need PurchasePrice since Text259 divides by PurchasePrice and Text263 multiplies by PurchasePrice, thius cancelling it out.

[Mpike926]

Hey guys, (maybe girl, june? lol)
Thanks for responding. I have tblloangeneral joined to tblcashflows with the formula calculated to sum up the income and minus it from the expenses. I did this expression in the field spot on the query. It looks like this
Expr1: Nz(Sum([Principal]+[Interest]+[fees]+[payoff]+[notesale]+[reonoi]))-Nz(Sum([priorliens]+[backtaxes]+[assetmgtfee]+[legalfc]+[loanadvances]+[reotaxins]+[reoothercost]+[brokerfees]+[ProfFeesOtherExp])).
This gives me the Net income across all portfolios. So I am definitely on the right track.
My only question is how to I set these calculations to be that of the specific ID? My final step is getting the cashflow for each ID.
When I click down the ID into a query pane..(I guess thats what you could call it, when i run the query it says, you tried to execute a query that does not include the specified expression 'id' as part of an aggregate function. SO im guessing I have to add the Id to the calculation, but not quit sure where it goes. I have attached a screenshot for reference.