Does Access Utitilize Formulas As Excel Does

[uneek78]

Does access have the ability to use formulas to calculate such as excel does? I am copying and pasting an excel spreadsheet I have into an access table. The excel spreadsheet has a formula:

=TEXT(D2-C2,"d:h:mm")

This formula calculates days/hours/minutes from 2 columns I have in the spreadsheet. Will the copy & paste carry over this formula? If not, is there anyway to input it into access so that it will continue to calculate the totals in the columns?

 

[ajetrumpet]

Does access have the ability to use formulas to calculate such as excel does?

mostly not.

I am copying and pasting an excel spreadsheet I have into an access table. The excel spreadsheet has a formula:

=TEXT(D2-C2,"d:h:mm")

Will the copy & paste carry over this formula?

no.

If not, is there anyway to input it into access so that it will continue to calculate the totals in the columns?

you will have to continue your quest of calculations through access queries.

i think this is all correct, but i'm only 99.9% sure.


Access Functions

Excel Functions

 

[uneek78]

So, do you think the access query will be able to successfully subtract the time/date in 2 columns and give a result?

 

[ajetrumpet]

So, do you think the access query will be able to successfully subtract the time/date in 2 columns and give a result?

yes. downfall is you will have to store the result in another column of the qry.

 

[uneek78]

Any idea how to do this? For better words: is there a simple way to do this?

 

[ajetrumpet]

i dont' really think there is uneek. but if so, you're welcome to post whta you have, and we can give a gander.

 

[MaliciousMike]

Is this a frequent task?

Is the time data always changing in the 2 fields within access?
What you could do is create an update query which re-calculates that column. Then create a macro which runs that query and tell access to run that macro on startup.

If you're frequently importing different data into this table, you will need to create a bit of code to import the spreadsheet into a temporary table then run a similar query on that temp table. Then have another query to import the records.

You could get really complex with this sort of thing but this would give similar results on a basic level.

Hope this helps.

 

[Len Boorman]

Never store something that you can calculate
Why
Because you then need to control any changes and ensure you update when anything changes. That is a bigger overhead that calculating whenever you want the value. Additionally it takes up storage and you may only want data for past 2 weeks this time, maybe next time 6 months.

Calculate what you want when you want it

L