Query problems when using Nz

[Jean]

Hi all,

I am trying to get this query right, but so far no joy...

I have written the following code in the Field box of the QBD grid of
a query:

firstvalue:IIf(Not (IsNull([Value1])),[Value1],(IIf(Not
(IsNull([Value2])),[Value2],(IIf(Not
(IsNull([Value3])),[Value3],[Value4])))))

What I actually want in field "firstvalue" is the left most value of
Value1, Value2, Value3 and Value4. E.g If for a record Value1 = 0 (or
NULL), then it must return Value2 or Value3 or Value4, depending on
the first one which is not 0 (or NULL).

I keep getting syntax errors, and specifically pointing to the first
comma in the code.
Can somebody please tell me what is wrong? I can email a sample DB to
someone if they want to check out the table which the query is based
on.

Thanks a bunch, in advance!

JP

[Tom van Stiphout]

On 5 Aug 2004 01:04:12 -0700, je**********@hotmail.com (Jean) wrote:

Proceed like this, so you can maintain your sanity. Don't try to write
the statement at once, but in several passes. In this example I'm
using "x" as a placeholder for future work.

Use Nz to convert both 0 and null values to 0.

If the Nz=0, do something I'll specify later, else return Value1:
FirstValue: iif(Nz(Value1)=0,x,Value1)

'Here I'm filling in "x" for the second pass.
FirstValue: iif(Nz(Value1)=0,iif(Nz(Value2)=0,x,Value2),Value1 )

You do the third and 4th pass, exactly the same way.

-Tom.

Hi all,

I am trying to get this query right, but so far no joy...

I have written the following code in the Field box of the QBD grid of
a query:

firstvalue:IIf(Not (IsNull([Value1])),[Value1],(IIf(Not
(IsNull([Value2])),[Value2],(IIf(Not
(IsNull([Value3])),[Value3],[Value4])))))

What I actually want in field "firstvalue" is the left most value of
Value1, Value2, Value3 and Value4. E.g If for a record Value1 = 0 (or
NULL), then it must return Value2 or Value3 or Value4, depending on
the first one which is not 0 (or NULL).

I keep getting syntax errors, and specifically pointing to the first
comma in the code.
Can somebody please tell me what is wrong? I can email a sample DB to
someone if they want to check out the table which the query is based
on.

Thanks a bunch, in advance!

JP

[John Turner]

je**********@hotmail.com (Jean) wrote in message news:<7e*************************@posting.google.c om>...

Hi all,

I am trying to get this query right, but so far no joy...

I have written the following code in the Field box of the QBD grid of
a query:

firstvalue:IIf(Not (IsNull([Value1])),[Value1],(IIf(Not
(IsNull([Value2])),[Value2],(IIf(Not
(IsNull([Value3])),[Value3],[Value4])))))

What I actually want in field "firstvalue" is the left most value of
Value1, Value2, Value3 and Value4. E.g If for a record Value1 = 0 (or
NULL), then it must return Value2 or Value3 or Value4, depending on
the first one which is not 0 (or NULL).

I keep getting syntax errors, and specifically pointing to the first
comma in the code.
Can somebody please tell me what is wrong? I can email a sample DB to
someone if they want to check out the table which the query is based
on.

Thanks a bunch, in advance!

JP

HI:
Try to past the following into the SQL view window.
DO NOT INCLUDE ANY NEW LINE MARKERS. The following
is all on one line. ( maybe that is where you had the problem )?

Assumed Table name = T
Assuemd Table Fields: named Value1, Value2, etc.

SELECT IIf(Not (IsNull(T.[Value1])),T.[Value1],(IIf(Not
(IsNull(T.[Value2])),T.[Value2],(IIf(Not
(IsNull(T.[Value3])),T.[Value3],T.[Value4]))))) AS firstvalue
FROM T;

I tested this on Access 97 and it returned the
expected value. However, if you wanted something
other than the complete field value, that is another
fish to fry

John

[James Fortune]

je**********@hotmail.com (Jean) wrote in message news:<7e*************************@posting.google.c om>...

Hi all,

I am trying to get this query right, but so far no joy...

If you still have trouble after applying Tom's advice, try placing the
following code in a module:

Public Function GetFirst(var1 As Variant, var2 As Variant, var3 As
Variant, var4 As Variant) As Variant
GetFirstValue = 0
If Nz(var1) <> 0 Then
GetFirst = var1
ElseIf Nz(var2) <> 0 Then
GetFirst = var2
ElseIf Nz(var3) <> 0 Then
GetFirst = var3
ElseIf Nz(var4) <> 0 Then
GetFirst = var4
End If
End Function

Then:

SELECT GetFirst([Value1],[Value2],[Value3],[Value4]) AS FirstValue
FROM tblValues;

James A. Fortune

[Jean]

Thanks for all the help everyone! I actually sorted this one out late
yesterday afternoon. :)

The problem I had was that I am using a German version of MS Access,
and somehow it did not understand the term Iif ("Wenn" in German).
I used this statement in the field box of the QBD grid:

firstvalue: Wenn(nz([value1])<>0;[value1];Wenn(nz([value2])<>0;[value2];Wenn(nz([value3])<>0;[value3];[value4])))

Strange, cause it can translate other terms, like Nz, True, IsNull
etc.

Well I got it to work now and would like to thank you all for being so
kind in helping me.

Regards,

Jean

ja******@oakland.edu (James Fortune) wrote in message news:<a6**************************@posting.google. com>...

je**********@hotmail.com (Jean) wrote in message news:<7e*************************@posting.google.c om>...

Hi all,

I am trying to get this query right, but so far no joy...

If you still have trouble after applying Tom's advice, try placing the
following code in a module:

Public Function GetFirst(var1 As Variant, var2 As Variant, var3 As
Variant, var4 As Variant) As Variant
GetFirstValue = 0
If Nz(var1) <> 0 Then
GetFirst = var1
ElseIf Nz(var2) <> 0 Then
GetFirst = var2
ElseIf Nz(var3) <> 0 Then
GetFirst = var3
ElseIf Nz(var4) <> 0 Then
GetFirst = var4
End If
End Function

Then:

SELECT GetFirst([Value1],[Value2],[Value3],[Value4]) AS FirstValue
FROM tblValues;

James A. Fortune