Help with formula

  • Thread starter Thread starter mjm697
  • Start date Start date
M

mjm697

I need help writing this up in an access formula. This is going in to a Query.

Here it is in excel formula

=IF(D11<6,Q11,0)



This is what I have in access.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)
 
I need help writing this up in an access formula. This is going in to a Query.

Here it is in excel formula

=IF(D11<6,Q11,0)



This is what I have in access.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)
Click to expand...

You need to balance the parenthesis. If you have 2 open ones you need
to have 2 closed ones.

try:
mjm: IIf([Transaction Errors]>=10,[Transaction Errors],0)

In the above expression, if the value in [Transaction Errors] is 10 or
more, the value in [Transaction Errors] will display, otherwise 0.
Is that what you want?
 
You have extra parentheses:
mjm: IIf([Transaction Errors]>=10,[Transaction Errors],0)

Depending on what you need to do, you may be able instead just to use >=10
as the criteria for [Transaction Errors].
 
That is a rather strange approach to parenthesizing.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)

Assuming what you want is to return in Query Field the value of the Field
"Transaction Errors" if there are ten or more, otherwise return a value of
zero, that should read:

mjm: IIF(NZ([Transaction Errors])>=10, NZ([Transaction Errors), 0 )

The entire argument list of the IIF function is parenthesized. The NZ
function converts Nulls to Zero, and its arguments are parenthesized also.
I used it on both the test for value, and the return value, because I am not
certain whether each argument is evaluated before the first argument is
applied. In general, any Null in a calculation will cause the calculation
to return Null, which I did not think was your desire.

If I have misunderstood what you want, or other details, post back, and
perhaps someone can help.

In general, it is a good idea to explain specifically what your question is,
and if something is going wrong, to describe it in detail. It's difficult
enough to debug remotely, but often impossible without information. In your
case, it seemed clear that the unorthodox use of parentheses was likely the
problem. I'm reasonably certain that Excel does not gladly suffer unbalanced
parentheses -- what would give you the idea that Acces would?

Larry Linson
Microsoft Office Access MVP
 
You are right Larry…

I did not explain my self correctly so here it goes. I am working on an
incentive database. In this database,

I have three table’s name [Transaction Errors], [Potential Payout] and
[Actual Incentive Calculation]. If [Transaction Errors] is greater than 6
error’s then [Actual Incentive Calculation] will be $0.0, but if [Transaction
Errors] is less than 6 errors then [Actual Incentive Calculation] we be
whatever amount is in [Potential Payout].

Can this be done? I’m really new at this and I’m trying to learn as I go so
please let me know if there’s any other information you need from me to
figure this out. Thanks so much.

Larry Linson said:
That is a rather strange approach to parenthesizing.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)

Assuming what you want is to return in Query Field the value of the Field
"Transaction Errors" if there are ten or more, otherwise return a value of
zero, that should read:

mjm: IIF(NZ([Transaction Errors])>=10, NZ([Transaction Errors), 0 )

The entire argument list of the IIF function is parenthesized. The NZ
function converts Nulls to Zero, and its arguments are parenthesized also.
I used it on both the test for value, and the return value, because I am not
certain whether each argument is evaluated before the first argument is
applied. In general, any Null in a calculation will cause the calculation
to return Null, which I did not think was your desire.

If I have misunderstood what you want, or other details, post back, and
perhaps someone can help.

In general, it is a good idea to explain specifically what your question is,
and if something is going wrong, to describe it in detail. It's difficult
enough to debug remotely, but often impossible without information. In your
case, it seemed clear that the unorthodox use of parentheses was likely the
problem. I'm reasonably certain that Excel does not gladly suffer unbalanced
parentheses -- what would give you the idea that Acces would?

Larry Linson
Microsoft Office Access MVP


mjm697 said:
I need help writing this up in an access formula. This is going in to a
Query.

Here it is in excel formula

=IF(D11<6,Q11,0)



This is what I have in access.

mjm: IIf(([Transaction Errors]>=10,([Transaction Errors],0)
Click to expand...
Click to expand...
 
mjm697 said:
You are right Larry.

. . . I am working on an incentive database.

I have three table's name [Transaction Errors], [Potential Payout]
and [Actual Incentive Calculation]. If [Transaction Errors] is
greater than 6 error's then [Actual Incentive Calculation] will be
$0.0, but if [Transaction Errors] is less than 6 errors then
[Actual Incentive Calculation] we be whatever amount is in
[Potential Payout].

Can this be done? I'm really new at this and I'm trying to
learn as I go so please let me know if there's any other
information you need from me to figure this out. Thanks
so much.
Click to expand...

You certainly can do it, but your description sounds very
"spreadsheet-like", as though your idea of a Table is more like column in a
spreadsheet.

A newsgroup response is not an adequate or appropriate place for a tutorial
on relational database structure... but, chances are, you really need to get
your data properly organized, defined, and structured before you proceed
with the "calculation part". You should have a Field for each of these
items, but certainly you should not (or extremely) rarely have one-field
tables. The information will have to also be identified by the
salesperson(?) and dates, and, very likely, totalled for some time period
for your calculation.

At http://appdevissues.tripod.com, in the downloads section, you'll find a
PowerPoint presentation I did for my user group on "Practical Table Layout".
If you don't have Microsoft PowerPoint, you can download the PowerPoint
viewer from the Microsoft website. Why don't you give that a look and post
back with a tenative data structure and layout... Tables, all the pertinent
Fields in the Tables, relationship between the tables, etc., and perhaps
someone can take a look at that to see that you are starting down the right
track.

Larry Linson
Microsoft Office Access MVP
 
Back
Top