Compare three fields to get one result.

  • Thread starter Thread starter Mary Ann Hailey
  • Start date Start date
M

Mary Ann Hailey

Good afternoon, Long time browser, first time emailer.... Thanks for your
help in past. I have come across something that I just cannot get worked
out. For sake of typing space, I omitted the [reimbexp]! tag on some of the
field below.

The [invamt] field needs looked at first, if it is null or =0, I want it to
look at the [acost], if [acost] has a value, I want to use [acost] first.
basically, If [invamt] is null or =0, then use [acost] unless it is null or
0, then use [ecost] unless [ecost] is =0 or null, then go back to use [invamt]

This was what I have so far. I will be using this query in another select
query to run some calculations based on the results the query works.

oblcost: IIf (([reimbexp]![invamt] = 0 Or [reimbexp]![invamt] is Null) or
(Iif( [reimbexp]![acost] =0 or [reimbexp]![acost] Is Null),
[reimbexp]![ecost]), [reimbexp]![invamt])))))

Any help or direction is very much appreciated. Thanks again
 
Try this ---
oblcost: IIf (Nz([reimbexp].[invamt], 0) = 0, Iif(Nz([reimbexp].[acost], 0)
<>0, [reimbexp].[acost], [reimbexp].[ecost]), [reimbexp].[invamt])

--
KARL DEWEY
Build a little - Test a little
Mary Ann Hailey said:
Good afternoon, Long time browser, first time emailer.... Thanks for your
help in past. I have come across something that I just cannot get worked
out. For sake of typing space, I omitted the [reimbexp]! tag on some of the
field below.
The [invamt] field needs looked at first, if it is null or =0, I want it to
Click to expand...
look at the [acost], if [acost] has a value, I want to use [acost] first.
basically, If [invamt] is null or =0, then use [acost] unless it is null or
0, then use [ecost] unless [ecost] is =0 or null, then go back to use [invamt]

This was what I have so far. I will be using this query in another select
query to run some calculations based on the results the query works.

oblcost: IIf (([reimbexp]![invamt] = 0 Or [reimbexp]![invamt] is Null) or
(Iif( [reimbexp]![acost] =0 or [reimbexp]![acost] Is Null),
[reimbexp]![ecost]), [reimbexp]![invamt])))))

Any help or direction is very much appreciated. Thanks again
Click to expand...
 
Your Or seems incorrect. As well, rather than checking for both =0 or Null,
why not use the Nz function to convert Nulls to 0?

oblcost: IIf (Nz([invamt],0) <> 0, [invamt], IIf(Nz([acost], 0) <> 0,
[acost], IIf(Nz([ecost],0) <> 0, [ecost],[invamt])))
 
Back
Top