Access program limit?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

fox - 11 Jan 2006 18:38 GMT

Hi!
I use a textbox to calculate the total value. Here is my code, when I use
3 standard, it works fine. However, now I add one more standard value, it
will crash. Did I reach the Access limit? Is any other way to solve? Thank
you.

=Sum(IIf([Standard]=0.1,(IIf(([A]*[Density]/100)>=0.08,0.08,([A]*[Density]/100))),(IIf([Standard]=0.2,(IIf(([A]*[Density]/100)>=0.15,0.15,([A]*[Density]/100))),(IIf([Standard]=0.3,(IIf(([A]*[Density]/100)>=0.20,0.20,([A]*[Density]/100))),(IIf([Standard]=0.4,(IIf(([A]*[Density]/100)>=0.37,0.37,([A]*[Density]/100))),0))))))))

Reply to this Message

fox - 11 Jan 2006 18:50 GMT

The logic is if the standard value is X, A*density value over the max value,
then use the max value, otherwise using the calculated value. And there is 4
standards.

Fox

Reply to this Message

Duane Hookom - 11 Jan 2006 20:38 GMT

Write a user-defined function in a module. Name your module
"modBusinessCalcs". Use the function in your query, in your control sources,
in your code, and in your application documentation.

I can't imagine attempting to maintain an expression like this in a query or
control source. If you get to more than 2 IIf()s, use code.

Signature

Duane Hookom
MS Access MVP
--

> Hi!
> I use a textbox to calculate the total value. Here is my code, when I
[quoted text clipped - 4 lines]
>
> =Sum(IIf([Standard]=0.1,(IIf(([A]*[Density]/100)>=0.08,0.08,([A]*[Density]/100))),(IIf([Standard]=0.2,(IIf(([A]*[Density]/100)>=0.15,0.15,([A]*[Density]/100))),(IIf([Standard]=0.3,(IIf(([A]*[Density]/100)>=0.20,0.20,([A]*[Density]/100))),(IIf([Standard]=0.4,(IIf(([A]*[Density]/100)>=0.37,0.37,([A]*[Density]/100))),0))))))))

Reply to this Message

fox - 12 Jan 2006 20:00 GMT

Yes, thank you very much. I learn another tip.........:>

> Write a user-defined function in a module. Name your module
> "modBusinessCalcs". Use the function in your query, in your control sources,
[quoted text clipped - 11 lines]
> >
> > =Sum(IIf([Standard]=0.1,(IIf(([A]*[Density]/100)>=0.08,0.08,([A]*[Density]/100))),(IIf([Standard]=0.2,(IIf(([A]*[Density]/100)>=0.15,0.15,([A]*[Density]/100))),(IIf([Standard]=0.3,(IIf(([A]*[Density]/100)>=0.20,0.20,([A]*[Density]/100))),(IIf([Standard]=0.4,(IIf(([A]*[Density]/100)>=0.37,0.37,([A]*[Density]/100))),0))))))))

Reply to this Message