Help to create Code for Excel Formula (1 Viewer)

[nexustech]

Hi Friends,

I will appreciate if you guy help me code this in the MS Access if i create a table as shown in the picture below.

=IF($D2="Not Required in Current FY","No Visit Required",IF(AND($A2<>TODAY(),$B2=""),"Needs Scheduled",IF(AND(ISNUMBER($B2),$C2=""),"Work Scheduled ",IF($C2=$B2,"Completed",IF($C2<=$B2,"Completed Earlier","Completed with Delay")))))

 

[Pat Hartman]

Replace the cell references with column names. References in excel are to a specific row AND a specific column. References in Access are to a specific column. The row is always assumed to be the current row since only one row is ever current when the function runs

=IIf(Comments="Not Required in Current FY","No Visit Required",IIf(DeadLine<>Date() And IsDate([Scheduled Date])),"Needs Scheduled",IIf(IsDate([Scheduled Date]) AND IsNull([Actual Completion Date])),"Work Scheduled ",IIf([Actual Completion Date]= [Scheduled Date],"Completed",IIf([Actual Completion Date]<=[Scheduled Date],"Completed Earlier","Completed with Delay")))))

That may do it but could be typos.

Just FYI, when you are working with a relational database rather than a spreadsheet, the data layer is separate from the presentation layer so it is best to use column and object names that VBA likes rather than using human friendly names. So, column and other object names should be formed from ONLY the letters a-z and A-Z; the numbers 0-9, and the underscore. Object names should start with a letter. They should never include embedded spaces or other punctuation characters even when Access lets you. And don't forget, to pay attention if Access complains a about a name you are using for a column to take it seriously and choose a different name. You want to always avoid the names of functions and properties. Especially Date, Name, Month, etc ALL will cause real problems with VBA that are very subtle.