Converting Excel Formulas to Access Expressions

[klvasquez]

I am trying to convert the following formulas into Access expressions:

=IF(ROWS($1:1)>MAX(G:G),"-",MATCH(ROWS($1:1)-1,G:G)+1)

=IF(OR(I2="",I2="-"),"",COUNTIF(I$2:I2,I2))



=IF(OR($I2="",$I2="-"),"",INDEX(A:A,$I2))

I tried several times to convert these but the "$1:!" part is where I run into trouble. Any help would be greatly appreciated.

Thanks in advance.

[Xipooo]

Honestly, this makes no sense at all. Are you trying to make a form? A Table? A report? A Query?

[klvasquez]

My apologies for not being very specific.

I am trying to accomplish what is covered in this thread on an excel forum. My dataset exceeds the maximum number of cells that Excel can hold so I am trying to do these calculations in Access as a Calculated Field. I am having trouble converting the syntax used in Excel v. Access.

Any input is appreciated.

[Xipooo]

Access does not hold data in the same way that Excel does. You would have to create tables in a normalized manner which holds the data for you. Then you can run queries against that data to display the information in the manner you wish to view it. You can then use the results of that query to either display it as a form, report, or you can just open the query in a data view. You can also export a query as an Excel spreadsheet.

There are calculations which can be made to create a field on a table, but 99.9% of the time you want to do calculations in the query, not the table.

[June7]

Describe the algorithm in plain English. What exactly are you trying to accomplish? Seems to me that reproducing what happens in the Excel (populating cells down the sheet) would require creating records in Access table. A very very different process.

Calculated Fields in table are very limited. Many intrinsic functions are not available and custom functions not at all.