Excel Formula in Access Table field

[chris M]

3/7/2008 7:26 AM PST

Question sorry, please ignore the previous post, here is the correct one

I am completely lost. I can do this in excel, but because the whole
application must be migrated to Access, I do not know how to do this. Any
help would be so appreciated I cant even begin to tell you.

In Excell, I have a column called "open" date, a column called "close" date,
and a column called "time between". For each record I put a date in
for Open date cell and the Close date cell. I have a formula that causes the
"time between" column to fill in automatically, as it calculates the number
of days between "open" and "close", taking into account weekends.

The Formula in the "time between" column is a canned Excell formula called
"Networkdays".
I have also put in a holiday range on my spreadsheet that lists our holidays
for the current year.

Here is the Excel formula "Networkdays", with my Holiday range put in:
Please ignore the cell address references:
=IF(AND(J7<>"",(NETWORKDAYS(J7,K7,spreadsheetname.xls!holidays))-1>=0),(NETWORKDAYS(J7,K7,spreadsheetname.xls!holidays)-1),"")

This formula works. But how do I set this up in a field table in Access?
Does Access know what the formula "Networkdays" from Excell is?

HELP!!!

Thanks, chris

 

[Jerry Whittle]

1. You can not put formulas in Access tables. The tables look like
spreadsheets, but are a completely different animal.

2. You can put formulas in queries to get the desired data returned. It's
even possible to use Excel functions, but somewhat difficult. Access has the
DateAdd function that might do what you need.

 

[bella.sandi]

1. You can not put formulas inAccesstables. The tables look like
spreadsheets, but are a completely different animal.

2. You can put formulas in queries to get the desired data returned. It's
even possible to use Excel functions, but somewhat difficult.Accesshas the
DateAdd function that might do what you need.
--
Jerry Whittle,MicrosoftAccessMVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.











- Show quoted text -

Hi Jerry,

I am working on an Access Data Base as well and need to incorporate an
auto dat/time stamp but do not know how. I did locate the format
function that allows for that type of data to be formatted correctly
but do not know how to enter the formula for this; can you help?

Thanks,
Sandi

 

[Jerry Whittle]

Hi Sandi,

One important thing to remember about Access dates and time is that the
format means little to what is actually stored in the table. Access stores
dates and times as a number. Formatting makes this number look like a date.
For example the following data and time:

3/10/2008 9:09:29 AM is stored as 39517.3815856481.

If you need an auto date/time stamp for new records, the easiest is to have
a new date/time field in the table and make th default value = Now().