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

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,876
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,626
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,095,338
Messages
5,443,903
Members
405,256
Latest member
ukboyme

This Week's Hot Topics

Top