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

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,843
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,612
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]
 

Watch MrExcel Video

Forum statistics

Threads
1,090,507
Messages
5,414,961
Members
403,557
Latest member
hsstrider

This Week's Hot Topics

Top