Query problems when using Nz

**Tom van Stiphout** · Nov 13 '05, 02:36 AM

Re: Query problems when using Nz

On 5 Aug 2004 01:04:12 -0700, jeanjanssens@ho tmail.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),V alue1)

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

-Tom.

[color=blue]
>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[/color]

**John Turner** · Nov 13 '05, 02:36 AM

Re: Query problems when using Nz

jeanjanssens@ho tmail.com (Jean) wrote in message news:<7e9b4d2.0 408050004.7ff39 2eb@posting.goo gle.com>...[color=blue]
> 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[/color]

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** · Nov 13 '05, 02:36 AM

Re: Query problems when using Nz

jeanjanssens@ho tmail.com (Jean) wrote in message news:<7e9b4d2.0 408050004.7ff39 2eb@posting.goo gle.com>...[color=blue]
> Hi all,
>
> I am trying to get this query right, but so far no joy...[/color]

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** · Nov 13 '05, 02:37 AM

Re: Query problems when using Nz

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

jafortun@oaklan d.edu (James Fortune) wrote in message news:<a6ed3ce7. 0408051228.4f93 ac5a@posting.go ogle.com>...[color=blue]
> jeanjanssens@ho tmail.com (Jean) wrote in message news:<7e9b4d2.0 408050004.7ff39 2eb@posting.goo gle.com>...[color=green]
> > Hi all,
> >
> > I am trying to get this query right, but so far no joy...[/color]
>
> 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[/color]

Query problems when using Nz

Query problems when using Nz

Comment

Comment

Comment

Comment