Help with formula combining query and table

[MNNovice]

I have a subform that is based on a query. I want a text box in this form which will give me a calculated figure.

what I want to say is this take the FedRatio from tblGrants and multiply Amount here.

The query is based on tables other than tblGrants. Is it possible to do this?

I know the simple calcualtion should be =[FedRatio] * [Amount] but how do I incorporate FedRatio from tblGrants?

Any help is appreciated. Thanks.

[beacon]

What is the record source for the subform?

There might be a better way to do this, and of course that depends on what your answer to my question is, but here's what I think you need to do.

If the record source for the subform is tblGrants, add an invisible textbox that has the control source of FedRatio and then use that in your expression.

[MNNovice]

The record source for the sub form is a query that's based on several table but NOT the tblGrants. And tblGrant is the one that has the FedRatio.

I am thinking since I need the calculation on a report, I may use tblGrant as one of the source plus the query. Let's see if it works.

Thanks.

[Denburt]

Which form are we talking about? Is the main form or the subform based on a query/table that has the GrantID in it? If so then you can add the Grant table in that query and pull the results that you are looking for. You could also use the Dlookup("FedRatio ","tblGrants","GrantID=" & me!cboGrants) However it would be better if you were able to use the first of the two methods since Dlookup can slow things down.

[NeoPa]

As Den says M, if there is a logical link between the query data and the particular record you need in tblGrants, then you surely need a query that includes tblGrants as a JOINed table in the FROM clause.

Domain Aggregate (DLookup() etc) functions can be used instead, but as Den so rightly says again, this is not to be advised unless unavoidable.

[MNNovice]

Denburt:

Happy Monday!

What I tried to do is to create a report instead. This report is based on a query and a tblGrants. But I get no data - meaning blank page. When I delete tblGrants, it works. But tblGrants is the table which contains the FedRatio that I will need to calculate the amount to charge.

What are my choices to resolve this matter. Thanks.

[Denburt]

We are still where we were when I asked about the logical link between the query and tblGrants what tables are in the query? Can you include tblGrants in that query (as Neo suggested)? Then you can create the form or report.

Oh n Happy Monday to you too.

[MNNovice]

Denburt:

If you can believe it - somehow it's working now without my doing anything...Go figure.

Oh well, your patience is much appreciated. Until my next question. Thanks again.

[Denburt]

That's great that it is working now. Glad we could be of help.