using an excel formula in an access table

theYaniac

New Member
Joined
Jan 7, 2018
Messages
34
I am very new to access and trying to build some calculated fields in an access table (MS Access 2016). I have the following formula in my excel table to calculate monthly rental for equipment/materials. I also have a formula that pro rates days in the month if the materials/equipment is dismantled or removed from rent within the current rental period/month.

Determines if there will be rental in the current period:

=IF(G9="","N",IF(I9<105,"N",IF(AND(I9<$G$9,I9>1),"N",IF(V9<$I$2,"Y","N"))))

Calculates Pro-Rated Days in the rental period:

=IF(H10=$J$2,0,IF(AND(K10>60,K10<121),(Y10-K10+60)*-1,IF(OR(AND(H10<$J$2,ISBLANK(I10)),X10="N"),0,IF(AND(H10>$J$2,I10<$L$2,I10>1),($L$2-$J$2-K10+1)*-1,IF(H10>$J$2,$J$2-H10,(($L$2-I10)*-1))))))

Any help on converting this to access would be greatly appreciated.

Thanks in advance
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,840
You must use calculated fields in a query.

Access does Date Math using these 2 functions:
DateAdd, or DateDiff

=DateDiff("d",[RentalDate],[ReturnDate])
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
Windows
THe first step will also be changing the cell references to column names.
For example (using a trivial example):

Excel:
=A1+A2

MSAccess:
[Cost]+[Tax] As [TotalCost]
 

Forum statistics

Threads
1,089,284
Messages
5,407,369
Members
403,138
Latest member
jay_py

This Week's Hot Topics

Top