Show calcualation from a query on a form.

[Jennifer K.]

Can I show a value that is calculated in a query in a form?

I have a table where data is entered using a form. I have a separate query
that calculates age based on two dates from the table. I would like to show
the calculated age on the form. I've been playing a bit with "subforms" but
that doesn't seem to be working exactly the way I was hoping. Can that be
done and how?

Thanks,
Jennifer

 

[Wayne-I-M]

In your query you have a culculated column

NewAge:1+1
or some other sum

Base the form on the query
In the field list you will see NewAge

Drag this into the form design

 

[Al Campagna]

Jennifer,
Yes you can.
Using the query design grid, and given fields named Price and Qty, a
calculated column in the query could be...
LineTotal : Price * Qty
LineTotal is now a "bound" field that will show up on the form design
Field List, and can be placed on the form just like any table field.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Jennifer K.]

This is so confusing!

The form is based on a Table. The query is calculating age from dates that
are in the table (that is filled with information entered into the form). I
would like to display the age that is calculated in the query from the dates
in the table on the form.

Oh boy!

Jennifer

 

[Wayne-I-M]

Why not just do the cacultion in the form instead of the query.

Open the form in design view
Select view
Select toolbox
Select Text box
Place it where you want on the form
Right click the new text box
Select properties
In the data column slect the countrol source row
add the calculation here (with = at the start of the calculation)

 

[Ken Sheridan]

Jennifer:

The short answer is Yes. You can look up the value computed in the query by
calling the DLookup function. If we assume the table has a primary key
column PersonID make sure the query includes this in its columns returned.
Lets also assume for this example that the query is called qryPeopleAges, and
the column it returns containing the age based on the two dates is called
MarriageAge Then for the ControlSource property of an unbound text box on the
form put:

=DLookup("MarriageAge","qryPeopleAges","PersonID = " & [PersonID])

A better way would be to join the table to the qryPeopleAges query on the
PersonID columns and, as well as returning all the columns from the table
also return the MarriageAge column from the query. You can then have a bound
text box in the form with MarriageAge as its ControlSource property.

Or, best of all, you can simply extend your query so it returns all the
columns from the table plus the MarriageAge column and then base the form on
the query.

Ken Sheridan
Stafford, England

 

[Jennifer K.]

Can I flatter you all with the response: Genius!!?

Thanks so much. All of those are clear and workable and very helpful.

Jennifer

Jennifer:

The short answer is Yes. You can look up the value computed in the query by
calling the DLookup function. If we assume the table has a primary key
column PersonID make sure the query includes this in its columns returned.
Lets also assume for this example that the query is called qryPeopleAges, and
the column it returns containing the age based on the two dates is called
MarriageAge Then for the ControlSource property of an unbound text box on the
form put:

=DLookup("MarriageAge","qryPeopleAges","PersonID = " & [PersonID])

A better way would be to join the table to the qryPeopleAges query on the
PersonID columns and, as well as returning all the columns from the table
also return the MarriageAge column from the query. You can then have a bound
text box in the form with MarriageAge as its ControlSource property.

Or, best of all, you can simply extend your query so it returns all the
columns from the table plus the MarriageAge column and then base the form on
the query.

Ken Sheridan
Stafford, England

Can I show a value that is calculated in a query in a form?

I have a table where data is entered using a form. I have a separate query
that calculates age based on two dates from the table. I would like to show
the calculated age on the form. I've been playing a bit with "subforms" but
that doesn't seem to be working exactly the way I was hoping. Can that be
done and how?

Thanks,
Jennifer