# using an excel formula in an access table

#### theYaniac

##### New Member
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.

#### ranman256

##### Well-known Member
You must use calculated fields in a query.

Access does Date Math using these 2 functions:

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

• theYaniac

#### xenou

##### MrExcel MVP, Moderator
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]

• theYaniac