Calculating functions in form objects

  • Thread starter Thread starter FredVN
  • Start date Start date
F

FredVN

Hi,



I'm an M/S Access newbie who has figured out how to do simple calculations
in a form objects using the control sources for a text boxes, but am having
problems using functions to calculate database values.



Table name is: Medicare Form



It is derived from a query named; Patient query



First I'd like to find the highest value in a field named: mcrec in any of
any records in the database.



I've tried; =Max(mcrec), but that seems to yield the value of mcrec in the
same record.



Second, I'd like to calculate the sum of the values in a field named McDed
for all records in which Serdt had the same year as Serdt in the record
being displayed.



I've tried =DSum("[McDed]","Patient Query", Year(SerDt)=Year("[SerDt]")),
but get a syntax error.



I'd be very appreciative if someone would show me the proper commands and
syntax to use for these calculations.



FredVN



PS in the process of playing with these functions I've done something to the
database that prevents me from entering data in the forms. When I try I get
a "dinging" sound. What did I do and how do I fix it?
 
FredVN said:
I'm an M/S Access newbie who has figured out how to do simple calculations
in a form objects using the control sources for a text boxes, but am having
problems using functions to calculate database values.

Table name is: Medicare Form

It is derived from a query named; Patient query

First I'd like to find the highest value in a field named: mcrec in any of
any records in the database.

I've tried; =Max(mcrec), but that seems to yield the value of mcrec in the
same record.

Second, I'd like to calculate the sum of the values in a field named McDed
for all records in which Serdt had the same year as Serdt in the record
being displayed.

I've tried =DSum("[McDed]","Patient Query", Year(SerDt)=Year("[SerDt]")),
but get a syntax error.

I'd be very appreciative if someone would show me the proper commands and
syntax to use for these calculations.

PS in the process of playing with these functions I've done something to the
database that prevents me from entering data in the forms. When I try I get
a "dinging" sound. What did I do and how do I fix it?
Click to expand...

The Max function (in header/footer) calculates the sum over
the records returnd by the form's record source table/query.
If you want the sum over all the records in the table, use
DSum. Not at all sure about the table, but it would be
like:
=DSum("mcrec", "nameof the table")

The other on would be more like:
=DSum("McDed","Patient Query", "Year(SerDt)=" & Year(SerDt))
 
FredVN said:
Table name is: Medicare Form

It is derived from a query named; Patient query

First I'd like to find the highest value in a field named: mcrec in any of
any records in the database.

I've tried; =Max(mcrec), but that seems to yield the value of mcrec in the
same record.
Click to expand...

In the form's recordsource, "Patient query", add a new column to the query
grid field.
MaxMcrec: = Max(mcrec). Bound the control to the new field.
 
Sorry, my mistake,

The expression will not work in your query.
 
Back
Top