Question How to Convert Excel Formula for Access Use

[JWPratt8]

Hi All,

I am relatively new to access and am trying to covnert a spreadsheet in Excel into an Access database. So far it has gone well until i have reached cells that have formulae. I haven't got the slightest idea of how to recreate this in access.

The the formulae in question are as follows:

=AND(AB2="Open",AF2>5,E2="")

=IF(D2<>"",IF(X2<>"","Closed","Open"),"")

=DAYS360(D2,X2)

=IF(YEAR(D2)<>1900,YEAR(D2),"")

=IF(YEAR(D2)<>1900,MONTH(D2),"")

=IF(X2="",NOW()-D2,0)

=IF(AC2<>"",IF(AC2>60,"TRUE","FALSE"),"")


(FYI I didn't create these formulae myself).

If it is possible to eplain what these formulae are actually saying (in newbie terms) that would be extremely helpful too.

Many Thanks,

James.

 

[CJ_London]

It is pretty straightforward you'll need to change your cell id's to the names of the fields in the table:

=AND(AB2="Open",AF2>5,E2="")
[AB2]="Open" and [AF2]<5 and [E2]=""

=IF(D2<>"",IF(X2<>"","Closed","Open"),"")
iif([D2]<>"",iif([X2]<.""."Closed","Open"),"")

=DAYS360(D2,X2)
- no equivalent, you would either use [D2]-[X2] or DateDiff("d",[D2],[X2]), but this does not assume all months are 30 days. However here are a couple of links you can adapt

http://www.vbaexpress.com/forum/showthread.php?t=25483
http://vbcity.com/forums/t/20694.aspx

=IF(YEAR(D2)<>1900,YEAR(D2),"")
iif(year([D2])<>1900,Year([D2]),"")

=IF(YEAR(D2)<>1900,MONTH(D2),"")
iif(year([D2])<>1900,Month([D2]),"")


=IF(X2="",NOW()-D2,0)
iif([X2]="",now()-[D2],0)

=IF(AC2<>"",IF(AC2>60,"TRUE","FALSE"),"")
iif([AC2]<>"",iif([AC2]>60,"True","False","")