Mary Ann,
Stick with me. We will eventually get this figured out.
1. First things first. When you reformat the following computed column
from
IIf(Nz(reimbexp!invamt,0)=0,IIf(Nz(reimbexp!ecost,0)<>0,reimbexp!acost,reimbexp!acost),0)
AS estinv,
What you get is:
IIf(Nz(reimbexp!invamt,0)=0,
IIf(Nz(reimbexp!ecost,0)<>0,
reimbexp!acost,
reimbexp!acost),
0) AS estinv,
To take it a step further, what you are really saying is:
If NZ(invAmt ,0) = 0 Then
IF NZ(ecost,0) <> 0 Then
estinv = acost
else
estinv = acost
endif
Else
estinv = 0
endif
The way I read this, if invamt is NULL or 0, then estinv = acost, otherwise
estinv = 0. I don't think this is really what you want, is it? I think this
is probably why the Switch function is not returning the value you think it
should.
Remember, the IFF statement looks like IIF(Expression, true value, false
value).
Why not reformat this as a switch statement as well. Since I don't have a
clue what you really want, I'll just substitute values that you can replace.
Switch(NZ(reimbexp.invamt, 0) <> 0, reimbext.invamt,
NZ(ecost,0) <> 0, [somevalue],
True, [anotherValue])
2. I see you took out the references to a table for your [estinv] computed
column. I believe that was the right call, but I am extremely confused by
your Group By clause. The last two elements of your Group By clause takes
up about 6 lines, and includes a bunch of If [X] IS NULL or [X] = 0 type
clauses rather than using the NZ function. It is also overly complicated
because of all of your nested IIF clauses. Actually, one of these is the
estinv column so making the change I recommended above should solve that
problem, but I would rewrite the last elements of the Group By clause as
Switch statements as well.
BTW, in that last element you have one value that uses:
ecost + (ecost * .09) + (acost * .075), one that uses:
acost + (acost * .09) + (acost * .075), another that uses:
ecost + (ecost * .09) + (ecost * .075), and finally:
acost + (acost * .075)
HTH
Dale
Mary Ann Hailey said:
the SQL is:
SELECT reimbexp.ID, reimbexp.element, reimbexp.invamt,
IIf(Nz(reimbexp!invamt,0)=0,IIf(Nz(reimbexp!ecost,0)<>0,reimbexp!acost,reimbexp!acost),0)
AS estinv,
Sum(Switch(reimbexp!invamt<>0,reimbexp!invamt,reimbexp!element='5316',([estinv]*factors.rate1+([estinv]*factors.rate2)),reimbexp!element='5737',([estinv]*factors.rate2),reimbexp.element='0716',([estinv]*factors.rate3)))
AS estbrdinv
FROM reimbexp LEFT JOIN factors ON reimbexp.element = factors.element
WHERE (((reimbexp.costype)<>"cx"))
GROUP BY reimbexp.ID, reimbexp.element, reimbexp.ecost, reimbexp.acost,
reimbexp.invamt,
IIf(Nz(reimbexp!invamt,0)=0,IIf(Nz(reimbexp!ecost,0)<>0,reimbexp!acost,reimbexp!acost),0),
IIf(reimbexp!invamt Is Null Or
reimbexp!invamt=0,Round((IIf(reimbexp!element='5316',(IIf(reimbexp!acost
Is
Null Or
reimbexp!acost=0,(reimbexp!ecost+(reimbexp!ecost*0.09)+(reimbexp!acost*0.075)),(reimbexp!acost+(reimbexp!acost*0.09)+(reimbexp!acost*0.075)))),IIf(reimbexp!acost
Is Null Or
reimbexp!acost=0,(reimbexp!ecost+(reimbexp!ecost*0.09)+([ecost]*0.075)),(reimbexp!acost+(reimbexp!acost*0.075))))),2),reimbexp!invamt);
Thanks!
Dale Fye said:
Mary Ann,
That is not a SQL statement. Where are you using this? Is it in a
control
source for a control on a form, or in a query? If in a query, the SQL
statments should look something like the following. The reason I ask is
that the way you have joined your tables may be effecting the results you
are getting. If in a query, please make the changes I've recommended
below,
and if it still does not work, go to the SQL view and copy the entire SQL
statement.
SELECT field, field2, Switch(.....) as estbrdinv
FROM reimbexp
INNER JOIN Factors
ON reimbexp.SomeField = Factors.SomeField
You still have a compination of ! and . delimeters between the table name
and the field name. Get rid of the ! and replace with .
I also don't know why you insist on putting all of the exta parenthesis
inside the switch statement. Extra parenthesis do nothing but make it
more
difficult to read. BTW, I've changed the line that checks for '5316'
from:
(reimbexp.[estinv]*[factors].[rate1]+(reimbexp.[estinv]*[factors].[rate2]))
to : reimbexp.estinv * (factors.rate1 + factors.rate2)
This is easier to read, and is mathematically the same.
I'll make my recommended changes for you to try again
Switch([reimbexp].[invamt]<>0,[reimbexp].[invamt],
[reimbexp].[element]='5316',[reimbexp].[estinv]*([factors].[rate1]+[factors].[rate2]),
[reimbexp].[element]='5737',[reimbexp].[estinv]*[factors].[rate2],
[reimbexp].[element]='0176',[estinv]*[factors].[rate3])
HTH
Dale
message
Sorry, the SQL is:
estbrdinv:
Switch([reimbexp]![invamt]<>0,[reimbexp]![invamt],[reimbexp]![element]='5316',([estinv]*[factors].[rate1]+([estinv]*[factors].[rate2])),[reimbexp]![element]='5737',([estinv]*[factors].[rate2]),[reimbexp].[element]='0176',([estinv]*[factors].[rate3]))
thanks
:
post the entire SQL statement.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
Dale, element is a text field- I don't get errors, but not the
correct
results either.
:
Is the Element field a text field, or numeric? If numeric, remove
the single
quotes from around the values in the Switch statement.
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
It almost worked - It calculated for the first condition (5316)
but
there
should have been results for 5737 and 0176. Its a start - I'll
keep at it.
Thanks
:
Mary Ann
The IIF (expression, true, false ) function requires 3
arguments.
All three
of these elements must be present. Based on the way you have
placed your
parentheses) , you have left off the IF False portion of
several
of the
statements.
I find that using the Switch function is easier than using
multiple nested
IIF statements. With the Switch function, you pass it a bunch
of
expressions, and the corresponding value if the statement is
true. Access
will process the list in the order it is received, and will
return the
"value" of the first expression it evaluates as True). If
none
of the
expressions evaluate to true, Access returns a NULL.
estbrdinv:
Switch(reimbexp.invamt <> 0, [reimbexp]![invamt],
reimbexp.element='3516',
reimbexp.estinv*(factors.rate1+factors.rate2),
reimbexp.element='5737', reimbexp.estinv*factors.rate2,
reimbexp.element='0716', reimbexp.estinv*factors.rate3)
--
HTH
Dale
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
:
I have a query that I am trying to compare fields and values
from two tables
and based on the results, run burdened calculations. If the
[invamt] is =0,
and the element code is 5316, calculate ([estinv]* [rate1])
+
[estinv*rate2]
and so on. Basically, the [invamt] already has the rates
included, and the
[estinv] doesn't and needs to. The factor table just has the
rates, .075
1.0212, etc. Here is the code:
estbrdinv:
Iif([reimbexp]![invamt]=0,round((Iif(reimbexp]![element]
='3516',
([reimbexp]![estinv]*[factors]![rate1]+([reimbexp]![estinv]*[factors]![rate2])))),
(Iif([reimbexp]![element] ='5737',
([reimbexp]![estinv]*[factors]![rate2]))),
(Iif([reimbexp]![element] ='0716',
([reimbexp]![estinv]*[factors]![rate3]))),2
[reimbexp]![invamt]))
I get wrong number of arguments error.
Thanks for any direction you can provide.
