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
 

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,808
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,577
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,085,022
Messages
5,381,258
Members
401,725
Latest member
Soly_Man

Some videos you may like

This Week's Hot Topics

Top