Access Formula (Access2000)

Topic

New Reply

Hello Access Gurus,

Is there a way I can create an array formula in Access. I know it’s possible in Excel using the Ctrl+Shft+Enter but what about Access?

Thanks a bunch!!

Reply | Quote

Replies

Hi,

At my knowledge the answer is no. But, what do you need an array formula in Access if I may ask?

Regards,

Nick

Reply | Quote

I have a table called “employment category” with column names PT, FT, VP, FP, UV, Dept#. I would like to create a formula called “fulltime employees” that will give me a sum of all FT and FP for a selected dept number.
such as: {=SUM(IF(dept#=7865,FT+FP,0))}

Please let me know if this example is confusing.

Thanks!

Reply | Quote

You’re confusing yourself. Even though an Access table *looks* much like an Excel spreadsheet, it is entirely different. Rows in Access tables are independent, not inherently ordered as in a spreadsheet, and an “array formula” is meaningless in an Access context.

First of all, you can’t base table fields on values in other fields. Second, You don’t store totals in a record. You need to get familiar with queries, which is where you would total up information like that when you need it, but you wouldn’t actually store those totals anywhere.

Reply | Quote

You should create a query where you will create a field for the sum of those two fields and then after in a form or report with record source based on that newly created query you will filter info based on your dept or whatever you want.

Regards,

Nick

Reply | Quote

You could use =dsum(“[FT]+[PV]”,”fulltime employees”,”Dept=7865″)

I think that is right but Charlotte will comment if I have too many quotes in it.

Reply | Quote

Do I look like the quotes monitor?? There’s nothing wrong with your formula. However, it can only be used in a form or in an expression in a query. You still can’t use it in a table.

Reply | Quote

Now that you mention it.

Just kidding.
Pat

Reply | Quote

Charlotte , Thanks

I agree you can’t use dsum in a table but then you woudn’t want to.

The question didn’t imply that. It just wanted a total, I assumed it would be in a control in a form.

NCordero

It is so easy to misunderstand what people really want. Starting to use Access is a tremendous leap from Excel and it takes quite a time to acquire the different way of looking at things. In Excel there is really only a cell to put things in. In Access they can be put in a table, a form or a report and you really have to understand the different purposes of each.

I hope you let us know if we gave you anything useful.

Reply | Quote