Anyone able to translate a formula from excel?

[dniezby]

I have to figure out how to get working in my application.

Here is the original forumula

round(if(r - (n * 0.3) < 0.51, 20 * (sqrt(sqrt(n) * n / r)) + 20, 20))

the value for "R" will be taken from this same record from a field called, "finish_position"
the "N" needs to come from a different table called, "events" field name "attendance"

I'm thinking that I first need to do a DLookup to assign the attendance to a variable.

varAtt = DLookup ("attendance","events", "event_id=" & event_id)

I tried to just replace the N and R with the field names but it was giving me an error.

Compile Error:
Expected: Expression

As I'm writing this, I think I might be able to break it up and put it into an "if then"

[rzw0wr]

Square root in Access is Sqr()

The rest should work.

Dale

[dniezby]

Square root in Access is Sqr()

The rest should work.

Dale

Thanks Dale, I'll give it a shot.

[dniezby]

Square root in Access is Sqr()

The rest should work.

Dale

I still get the same error..then it highlights the IF in my statement as noted by the colored section.

here is where I'm at right now.

Private Sub finish_position_Change()
varAtt = DLookup("attendance", "events", "event_id=" & event_id)

finish_points = round(if(finish_position - (varAtt * 0.3) < 0.51, 20 * (sqr(sqr(varAtt) * varAtt / finish_position)) + 20, 20))
end sub

[ssanfu]

You need to use immediate if function >> IIF(condition, Value if TRUE, Value if FALSE )

[rzw0wr]

Thanks ssanfu.
I missed that.

Dale

[dniezby]

You need to use immediate if function >> IIF(condition, Value if TRUE, Value if FALSE )

I have no idea what that means. So how do I translate that my formula???

[rzw0wr]

round(if(r - (n * 0.3) < 0.51, 20 * (sqrt(sqrt(n) * n / r)) + 20, 20))

Round(Iff(-(N*0.3) < 0.51, 20* (sqr(sqr(n)*n/r))+20,20))

Basically just change the If to Iff.

Untested. Just put into an Iff() statement.

Dale

[dniezby]

Round(Iff(-(N*0.3) < 0.51, 20* (sqr(sqr(n)*n/r))+20,20))

Basically just change the If to Iff.

Untested. Just put into an Iff() statement.

Dale

Giving it a test now. Thanks everyone.

[ssanfu]

Round(Iff(-(N*0.3) < 0.51, 20* (sqr(sqr(n)*n/r))+20,20))

That should be IIf()

IIF(condition, Value if TRUE, Value if FALSE )

This is the general syntax if the immediate if function ...

[dniezby]

Okay, that seems to work. Now I just need to put it in the correct event trigger.