haveing trouble translating this into a Access table field

[Sigzop]

I want Access to do this equation that works in excel. I was using the expression builder yet cannot get it to replicate. These are both the fields I need converted:




=IF(AND($D2<G$1,$E2>F$1)=TRUE,($B2*$C2)*IF(AND($D3 <G$1,$D3>F$1)=TRUE,1-($D3-F$1)/30,1)*IF(AND($E4<G$1,$E4>F$1)=TRUE,1-(G$1-$E4)/30,1),0)



and

=IF(AND($D3<H$1,$D3>G$1)=TRUE,1-($D3-G$1)/30,1)

[JeffChr]

Access won't recognize cell references. Those equations will never work in Access.

[ssanfu]

A datasheet in Access MIGHT look like a spreadsheet, but it is a totally different animal.

The IF statements might be able to be converted from Excel to Access, but it will take a lot of work... and probably some UDFs.

The main problem is that some of the data is coming from different rows/columns. Access doesn't work that way. Tables in Access have no inherent order, so there is not a "next" row.

The calculation in Excel has data from 6 different columns and 4 different rows. Being able to describe what the calculation is doing helps a lot.
What is the column header that is column D? Column E?

I have done this before. As I said, it is a lot of work and requires a really good understanding of the process.