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 find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,842
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,608
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,249
Messages
5,413,305
Members
403,474
Latest member
Rampestamper

This Week's Hot Topics

Top