Store Calculation from text box in table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jenna - 24 Jul 2007 15:44 GMT

I know it is not generally a good idea to store calculations in a table but
for my application there is no way around it i need to store the difference
of 2 times. Many people use the table resulting from the form and it is
outputed to excel alot. So is there anyways at all to get the calculation to
store in the underlying table?

Reply to this Message

scubadiver - 24 Jul 2007 15:50 GMT

"Many people use the table" ?? As far as I am aware tables should never be
touched.

You are better off doing the calculation in a query and allowing users to
export information through a form.

Signature

www.ae911truth.org

> I know it is not generally a good idea to store calculations in a table but
> for my application there is no way around it i need to store the difference
> of 2 times. Many people use the table resulting from the form and it is
> outputed to excel alot. So is there anyways at all to get the calculation to
> store in the underlying table?

Reply to this Message

Jenna - 24 Jul 2007 16:02 GMT

The table records events that have occured, it is very large and people
export it to do graphs for analysis and reports. Is there a way to get the
from to export a large amout of records into a excel sheet?

> "Many people use the table" ?? As far as I am aware tables should never be
> touched.
[quoted text clipped - 7 lines]
> > outputed to excel alot. So is there anyways at all to get the calculation to
> > store in the underlying table?

Reply to this Message

Klatuu - 24 Jul 2007 16:06 GMT

Yes, you create a query that has the calculation in it, then you use it in
the Table Name argument of the TransferSpreadsheet method to export it to an
Excel file.
The only justification for ever storing a calculated value in a table is
when the data necessary to produce the calculation will never be available
again. In this case, you have the two times and can easily reproduce the
calculation.

Signature

Dave Hargis, Microsoft Access MVP

> The table records events that have occured, it is very large and people
> export it to do graphs for analysis and reports. Is there a way to get the
[quoted text clipped - 11 lines]
> > > outputed to excel alot. So is there anyways at all to get the calculation to
> > > store in the underlying table?

Reply to this Message

John W. Vinson - 24 Jul 2007 17:16 GMT

>The table records events that have occured, it is very large and people
>export it to do graphs for analysis and reports. Is there a way to get the
>from to export a large amout of records into a excel sheet?

Use a Query instead of the Table. It exports to Excel in exactly the same way.

John W. Vinson [MVP]

Reply to this Message

scubadiver - 25 Jul 2007 07:38 GMT

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "QUERYNAME", _
"EXPORT LOCATION", , "NAME OF SHEET IN WORKBOOK"

Change those in capitals and put the code in the "on click" event in the
button. You can have as many as you like in the same event.

Signature

www.ae911truth.org

Reply to this Message