Infectious Expression Error on Form With No Apparent Cause

[accessbeginner12]

Hi,

I’m new to Access but needed to start using it due to Excel’s max row limitation. I’ve been trying to perform calculations similar to Excel using the Expressions feature in the Forms - for instance, one of the calculations is to find the average of a field for records that meet 2 different criteria.

I had pretty good success figuring everything out but as I continued building expressions on a single form, suddenly all of them would return #Error. Expressions that had previously worked would now return #Error.

In addition, I’ve created new forms and new expressions in those forms that work fine. However, when I copy the expression to the original form with the many calculations, it would return #Error.



When I copy the calculation boxes from the old form to the new form, it causes the box that was working on the new form to return #Error.

I’m not sure if there’s some sort of underlying setting that I’m getting wrong or if the volume of records (2M) combined with the comlpexity of the expressions is causing this, but any help would be greatly appreciated. I haven’t been able to find anything online or on the forums related to this particular issue.

Ultimately, I would like to be able to create a summary table like I can in Excel with 150 or so unique and complex calculations based on the database. My initial research into Access made it seem that Expressions on Forms were the way to go but if you have any suggestions for a better way to do that, I would love to hear them.

Thanks!
Kevin

[June7]

Would have to review data and analyse expressions. If you want to provide db, follow instructions at bottom of my post.

[accessbeginner12]

Would have to review data and analyse expressions. If you want to provide db, follow instructions at bottom of my post.

Hey, thanks for offering to help. I ended up spending a bit more time working on it since in order to provide the file, I would have to convert the data to something meaningless and then redo the formulas. I think I figured it out. Turns out my syntax was wrong on one of the formulas, which apparently caused all of the formulas to give the #Error message, while I was expecting just the box with the wrong syntax to error.

My problem with syntax was that I was trying to combine too many AND and OR operators, when I did not know the glory of IN. I probably spent an hour yesterday trying to figure out how to combine them, but to no avail, so I was pretty pumped when I stumbled upon it today. Also, for some reason, in order to get the formulas to refresh and not return the error, I have to go to the spreadsheet and "convert" the numbers to the same format by highlighting the column and selecting the format they are currently in. I'm sure there's a better solution but not a big deal.

In any case, thanks for offering to help!
Kevin