How do I get a calculation field to equal a record field?

[Guest]

How do I get a calcaulation field in a form into a field in a record ie: a
field holding a date and putting it into a field in a record that is named
date? Or takeing a field "lastname" and a field "firstname" join them
together and save then into a record call joined?

 

[fredg]

How do I get a calcaulation field in a form into a field in a record ie: a
field holding a date and putting it into a field in a record that is named
date? Or takeing a field "lastname" and a field "firstname" join them
together and save then into a record call joined?

You shouldn't.
1) As long as you have a LastName and a FirstName field stored in a
table, that's all you need. Any time you need to put the 2 fields
together, do it in unbound control on a form, in a report
(=[FirstName] & " " & [LastName]) or in a caclulated field in a query.
But there is no need to save it in your table.

The same would hold true if you were calculating a date from a date
stored in your table.

2) If you really do have a field named Date, Date is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

Thanks Fredg, but it does not help I need to do some other calculation that
do need to be saved to a file. I was just useing that as an example one file
has a balance due amount and I input a new payment how do I get the new
Balance due amount to be saved in the table? This is where I'm stuck I can
get everything to look great on the screen but can't put it into the file.

Jer

How do I get a calcaulation field in a form into a field in a record ie: a
field holding a date and putting it into a field in a record that is named
date? Or takeing a field "lastname" and a field "firstname" join them
together and save then into a record call joined?

You shouldn't.
1) As long as you have a LastName and a FirstName field stored in a
table, that's all you need. Any time you need to put the 2 fields
together, do it in unbound control on a form, in a report
(=[FirstName] & " " & [LastName]) or in a caclulated field in a query.
But there is no need to save it in your table.

The same would hold true if you were calculating a date from a date
stored in your table.

2) If you really do have a field named Date, Date is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[Guest]

Fredg is correct. There really is never any justification for storing a
calculated value in a field. You do the calculation when you want to present
the number to a user on a form or report. If the calculated value needs to
be available for a query, do the calculation in the query.

If you have a common calculation that has to be done in various places in
the application, write a Public function in a standard module and use the
function for the calculation. You only have to write in once and the results
is always the same.

If a value has to be calculated and that value has to be available for other
calculations at various places in the application, make your calculation
function a static function.

As you can see, there are plenty of ways to make your calculated value
available so that you don't have to store a calculated value. The problems
with using calculated values are:
1. They take up space in the database
2. Retrieving the value is slower. Almost any calculation is faster than a
disc fetch
3. At some point in time, it is highly likely one of the values on which the
calculated value will change and the stored calculation will then be
incorrect.

If you still think it is necessary to store the value, you really need to
reconsider your design.

Thanks Fredg, but it does not help I need to do some other calculation that
do need to be saved to a file. I was just useing that as an example one file
has a balance due amount and I input a new payment how do I get the new
Balance due amount to be saved in the table? This is where I'm stuck I can
get everything to look great on the screen but can't put it into the file.

Jer

How do I get a calcaulation field in a form into a field in a record ie: a
field holding a date and putting it into a field in a record that is named
date? Or takeing a field "lastname" and a field "firstname" join them
together and save then into a record call joined?

You shouldn't.
1) As long as you have a LastName and a FirstName field stored in a
table, that's all you need. Any time you need to put the 2 fields
together, do it in unbound control on a form, in a report
(=[FirstName] & " " & [LastName]) or in a caclulated field in a query.
But there is no need to save it in your table.

The same would hold true if you were calculating a date from a date
stored in your table.

2) If you really do have a field named Date, Date is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

 

[fredg]

Thanks Fredg, but it does not help I need to do some other calculation that
do need to be saved to a file. I was just useing that as an example one file
has a balance due amount and I input a new payment how do I get the new
Balance due amount to be saved in the table? This is where I'm stuck I can
get everything to look great on the screen but can't put it into the file.

Jer

How do I get a calcaulation field in a form into a field in a record ie: a
field holding a date and putting it into a field in a record that is named
date? Or takeing a field "lastname" and a field "firstname" join them
together and save then into a record call joined?

You shouldn't.
1) As long as you have a LastName and a FirstName field stored in a
table, that's all you need. Any time you need to put the 2 fields
together, do it in unbound control on a form, in a report
(=[FirstName] & " " & [LastName]) or in a caclulated field in a query.
But there is no need to save it in your table.

The same would hold true if you were calculating a date from a date
stored in your table.

2) If you really do have a field named Date, Date is a reserved
Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

I know it's difficult for all opf us to get our mind out of a
particular mind-set, but try.

Store the amount of the sale, store the amount received. Then, when
needed, calculate, but do not store, the difference. You can do that
in a query, on a form or on a report

Invoice amount is $200. Store it.
Amount received is $ 50. Store it.

Using an unbound control on a form that displays the InvoiceID number,
the balance due is calculated for that particular Invoice Number:

=DLookUp("InvoicedAmount]","OrdersTable","[InvoiceID] = " &
[InvoiceID]) - DSum("[AmtReceived]","PaymentsRcvdTable","[InvoiceID] =
" & [InvoiceID])

Balance amount is 200 - 50 = 150.

Receive a payment of $120.
Balance amount is now 200 - 170 = 30.

You can use a similar method (using two DSum()'s) to determine the
total amount due by Customer, not just by Invoice.