Question Excel formula convert to access

[Joe8915]

This should be a no brainer for some people. I am trying to convert a excel formula to access.


The column for h/c adj$ reads:

=IFERROR(TRUNC(D1024*(F1024*G1024/E1024-G1024),2),0)


Col: D1024 E1024 F1024 G1024
qty: act h/c: std h/c: rate: h/c adj$:
detail: 14.00 5.00 6.00 $2.00 $5.60


In access I have, with the ending result of $0.40 in the h/c adj$ txtfield
=([stdhc]*[RATE]/[ACTHC]-[RATE])

Where did I go wrong?

 

[Pat Hartman]

Your excel forumula has 5 arguments but your access formula has 4. You have identified 4 variables but supplied 5 values.

In addition, you need to be consious of the order of operations. Multiplication and division happen before addition and subtraction so you will probably need to use parentheses to make sure your formula is evaluated the way you want it to be.

 

[Joe8915]

Good point Pat, let me take a look at the aspect as well

 

[Joe8915]

Pat, this is a somewhat of a double post. but I thought i let you know what i came up with. I use this code to fix the problem, but when I did I received a error of overflow with error 2950.

Here is what code I used:

Summary detail: Sum(Fix(100*[dqty]*([dstdhc]*[drate]/[dacthc]-[drate]))/100)

 

[Pat Hartman]

Take a look at my parentheses because that is how the expression is being evaluated.

Sum(Fix(100*[dqty]*(([dstdhc]*[drate]/[dacthc])-[drate]))/100)

 

[Joe8915]

Pat, thanks ever so much for your help. Here is the bottom line I have been told in some cases these *Fields* are *Zero*, and *division by Zero* is illegal. So I hope I can get a work around.

Once again thanks

 

[Pat Hartman]

Use the IIf() function to test the divisor for zero or null. If it is a valid value, do the calculation. If not, return null, 0 or some other value.

 

[Joe8915]

Pat, good thought as well. I tried the IIf statement work great, but I have other information in the table that has to be calulated. I have this working in subform, therefore there is additional information that I need to calculate.

I used the following in a query:
IIf(Nz([wvstdhc],0)=0,'Divide by Zero',[WVrate]/[wvacthc])
Then I have in criteria field Not like "Divide by Zero" So naturally I have other information that I need in that subform. So you think I am going all about this wrong? At this time I can not think of any other way of doing this. If you like I can upload the db.
I know this isn't your first barbecue, maybe point me in another direction other then front door.