display one of the the multiple fields in query

[gykiang]

Hi

I need help to write the expression of a conditional field ([InsCL]) in my query table to display either one of the six following fields in the same query table:



[INVHI]
[INVCL]
[INVCL1]
[INVCL2]
[INVCL3]
[INVCL4]

The criteria as follows :

Condition 1: If all fields of [INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVHI]
Condition 2: If fields of [INVCL1], [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVCL]
Condition 3: If fields of [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVCL1]
Condition 4: If fields of [INVCL3], [INVCL4] contains null value, the field InsCL should equal [INVCL2]
Condition 5: If only field of [INVCL4] contains a null value, the field InsCL should equal [INVCL3], else InsCL should equal [INVCL4]
I tried with below expression but the control doesn't work as the value of InsCL get stuck with [INVHI].
InsCL: IIf(IsNull([INVCL1]+[INVCL2]+[INVCL3]+[INVCL4]),[INVHI] IIf(IsNull([INVCL1]+[INVCL2]+[INVCL3]+[INVCL4]),[INVCL], IIf(IsNull([INVCL2]+[INVCL3]+[INVCL4]),[INVCL1], IIf(IsNull([INVCL3]+[INVCL4]),[INVCL2],IIf(IsNull(INVCL4]),[INVCL3],[INVCL4])))))

+ was then replaced with "And", but still failed to get what I want.

Any clue to the above problem ?

Regards
Kiang

[ranman256]

Build this function in a module, then In your query, make a virutal field: GetInsCLValue([INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4], [INVHI]) as InsCL

public function GetInsCLValue(pvINVCL,pvINVCL1, pvINVCL2, pvINVCL3, pvINVCL4,pvINVHI)
dim vRet
select case true
   case isnull( pvINVCL) and isnull( pvINVCL1) and isnull( pvINVCL2) and isnull( pvINVCL3) and isnull( pvINVCL4)
      vRet = pvINVHI
 
  case  isnull( pvINVCL1) and isnull( pvINVCL2) and isnull( pvINVCL3) and isnull( pvINVCL4)
      vRet = pvINVCL
   case isnull( pvINVCL2) and isnull( pvINVCL3) and isnull( pvINVCL4)
      vRet = pvINVHI1
   case  isnull( pvINVCL3) and isnull( pvINVCL4)
      vRet = pvINVHI2
   case   isnull( pvINVCL4)
      vRet = pvINVHI3
end select
GetInsCLValue= vRet
end function

[June7]

Looks like non-normalized data structure is at root of this issue. Multiple similar name fields indicates non-normalized structure.

The nested IIf is missing a comma after [INVHI]. This expression should error and not return anything. The first 2 conditions are identical, I think the first one is missing INVCL in the sum.

Be aware that arithmetic with Null returns Null. If any one term in the sum is Null the answer will be Null, therefore the expression will always return [INVHI], unless every term is not Null.

Maybe you want:

Nz(INVCL, Nz(INVCL1, Nz(INVCL2, Nz(INVCL3, Nz(INVCL4, INVHI)))))

[gykiang]

[QUOTE=ranman256;243257]Build this function in a module, then In your query, make a virutal field: GetInsCLValue([INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4], [INVHI]) as InsCL

Hi ranman256,

I have established the function as module 2, copy and paste below code in a new field of the query table in design view :

GetInsCLValue([INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4], [INVHI]) as InsCL

I got error message of "The expression you entered contains invalid syntax".

How do I get around this ?

Thanks & regards
Kiang

[gykiang]

Looks like non-normalized data structure is at root of this issue. Multiple similar name fields indicates non-normalized structure.

The nested IIf is missing a comma after [INVHI]. This expression should error and not return anything. The first 2 conditions are identical, I think the first one is missing INVCL in the sum.

Be aware that arithmetic with Null returns Null. If any one term in the sum is Null the answer will be Null, therefore the expression will always return [INVHI], unless every term is not Null.

Maybe you want:

Nz(INVCL, Nz(INVCL1, Nz(INVCL2, Nz(INVCL3, Nz(INVCL4, INVHI)))))

Thanks June7. The fields contain of text only.

[June7]

Then the + character will concatenate. The & is preferred operator for concatenation.

If any term of concatenation expression is not null then the result will be the text value.

Does the nested Nz solve this issue?

[gykiang]

Then the + character will concatenate. The & is preferred operator for concatenation.

If any term of concatenation expression is not null then the result will be the text value.

Does the nested Nz solve this issue?

I have fixed some careless error of comma etc and nested the field with Nz:

InsCL: IIf(IsNull(Nz([INVCL])& Nz([INVCL1]) & Nz([INVCL2]) & Nz([INVCL3]) & Nz([INVCL4])),Nz([INVHI]),IIf(IsNull(Nz([INVCL1])& Nz([INVCL2]) & Nz([INVCL3]) & Nz([INVCL4])),Nz([INVCL]),IIf(IsNull(Nz([INVCL2])& Nz([INVCL3]) & Nz([INVCL4])),Nz([INVCL1]),IIf(IsNull(Nz([INVCL3])& Nz([INVCL4])),Nz([INVCL2]),IIf(IsNull(Nz([INVCL4])),Nz([INVCL3]),Nz([INVCL4])))))

Still [InsCL] returns to empty field, even though all the fields are not null.

I wonder how Nz(INVCL, Nz(INVCL1, Nz(INVCL2, Nz(INVCL3, Nz(INVCL4, INVHI))))) can be fitted into the above expression ?

[June7]

That expression won't give you what you want. The concatenated value will never be Null because Nz converts Null to empty string. Test for empty string.

IIf(Nz([INVCL]) & Nz([INVCL1]) & Nz([INVCL2]) & Nz([INVCL3]) & Nz([INVCL4])="", [INVHI] ...

Just replace the entire nested IIf() expression with the nested Nz() and see what happens. No VBA is needed with this approach.

[ranman256]

in the query field its just: GetInsCLValue([INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4], [INVHI])

[gykiang]

I copied & pasted GetInsCLValue([INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4], [INVHI]) as it is in design view of query table and Access auto changed it to :

Expr1: GetInsCLValue([INVCL],[INVCL1],[INVCL2],[INVCL3],[INVCL4],[INVHI])

when I clicked datasheet view, error message of undefined function of 'GetInsCLValue' in expression appeared.

[gykiang]

I have done this :

InsCL: Nz(IIf(IsNull([INVCL] & [INVCL1] & [INVCL2] & [INVCL3] & [INVCL4]),[INVHI],IIf(IsNull([INVCL1] & [INVCL2] & [INVCL3] & [INVCL4]),[INVCL],IIf(IsNull([INVCL2] & [INVCL3] & [INVCL4]),[INVCL1],IIf(IsNull([INVCL3] & [INVCL4]),[INVCL2],IIf(IsNull([INVCL4]),[INVCL3],[INVCL4]))))))

Only Condition 5 of below works.

Condition 1: If all fields of [INVCL],[INVCL1], [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVHI]
Condition 2: If fields of [INVCL1], [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVCL]
Condition 3: If fields of [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVCL1]
Condition 4: If fields of [INVCL3], [INVCL4] contains null value, the field InsCL should equal [INVCL2]
Condition 5: If only field of [INVCL4] contains a null value, the field InsCL should equal [INVCL3], else InsCL should equal [INVCL4]

[June7]

Have you tried the nested Nz()?

InsCL: Nz(INVCL, Nz(INVCL1, Nz(INVCL2, Nz(INVCL3, Nz(INVCL4, INVHI)))))

Or if I have the order of priority backwards try:

InsCL: Nz(INVCL4, Nz(INVCL3, Nz(INVCL2, Nz(INVCL1, Nz(INVCL, INVHI)))))

[gykiang]

I pasted InsCL: Nz(INVCL, Nz(INVCL1, Nz(INVCL2, Nz(INVCL3, Nz(INVCL4, INVHI))))) in Design view of query table, only Condition 2 worked with [INVCL] was displayed. When [INVCL] is null, [INVHI] will not appeared etc.

Condition 2: If fields of [INVCL1], [INVCL2], [INVCL3], [INVCL4] contain null value, then the field InsCL should equal [INVCL]

[June7]

Read post 12 again.

[gykiang]

Tried backward : InsCL: Nz(INVCL4, Nz(INVCL3, Nz(INVCL2, Nz(INVCL1, Nz(INVCL, INVHI)))))

Only Condition 5 worked.

Condition 5: If only field of [INVCL4] contains a null value, the field InsCL should equal [INVCL3], else InsCL should equal [INVCL4]