IIF and Nz in a query

  • Thread starter Thread starter Damien Dutreil
  • Start date Start date
D

Damien Dutreil

Hi,
In a request, I want to have in the Where something like:

[FORMS]![TRI]![AN1] ou [FORMS]![TRI]![AN2] ou
IIf(Nz(FORMS]![TRI]![AN1])+Nz(FORMS]![TRI]![AN2])=0,"*","")

Unfortunatly, syntax error on IIf.
I simplify the request and
IIf (5=4, "*", "") is also syntax error.

I don't understand this error because the syntax seems OK.
How can I specify in the Where that I want Field1 or Field2 or "*" if
Field1 and Field2 are null ?
Thanks for your answers.
 
Damien

I'm not sure why your "IIF(5=4, "*","") isn't working, but I'm also not sure
where you are putting the expression or the more complex version.

I can point out that the Nz() function takes two expressions/arguments - the
first is the field, control, or value being evaluated, and the second is
what a null value should be displayed as. Your expression

...Nz(FORMS]![TRI]![AN1])...

is missing the second argument. I suspect this converts a Null to ... a
Null! Your larger expression appears to be comparing to zero (0).
 
Use the IsNull function rather than the NZ function:

IIF(IsNull([YourField]),ValueIfNull,ValueIfNotNull)
 
Jeff said:
Damien

I'm not sure why your "IIF(5=4, "*","") isn't working, but I'm also not sure
where you are putting the expression or the more complex version.

I can point out that the Nz() function takes two expressions/arguments - the
first is the field, control, or value being evaluated, and the second is
what a null value should be displayed as. Your expression

...Nz(FORMS]![TRI]![AN1])...

is missing the second argument. I suspect this converts a Null to ... a
Null! Your larger expression appears to be comparing to zero (0).
Click to expand...

are you sure ?
the documentation says:

Access: Nz Function

The Nz function lets you return a value when a variant is null.

The syntax for the Nz function is:

Nz ( variant, [ value_if_null ] )

variant is a variable that is a variant datatype.

value_if_null is optional. It is the value to use when the variant is a
null value. If this parameter is omitted and the variant is a null
value, the Nz function will return a zero or a zero-length string.
 
PC said:
Use the IsNull function rather than the NZ function:

IIF(IsNull([YourField]),ValueIfNull,ValueIfNotNull)
Click to expand...

thans but always syntax error with only:
IIF(IsNull([FORMS]![TRI]![AN1]),"*","")
 
Damien said:
PC said:
Use the IsNull function rather than the NZ function:

IIF(IsNull([YourField]),ValueIfNull,ValueIfNotNull)
Click to expand...

thans but always syntax error with only:
IIF(IsNull([FORMS]![TRI]![AN1]),"*","")
Click to expand...

The correct syntax is IIF(IsNull([FORMS]![TRI]![AN1]);"*";"")

with ; instead of ,
It is very strange but it works :-)
 
That sounds like your Regional Settings use semicolon as
the list separator, not comma.

Check the Regional Settings of your OS if you normally use
comma as the list separator.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Damien said:
PC said:
Use the IsNull function rather than the NZ function:

IIF(IsNull([YourField]),ValueIfNull,ValueIfNotNull)
Click to expand...

thans but always syntax error with only:
IIF(IsNull([FORMS]![TRI]![AN1]),"*","")
Click to expand...

The correct syntax is IIF(IsNull([FORMS]![TRI]! [AN1]);"*";"")

with ; instead of ,
It is very strange but it works :-)
.
Click to expand...
 
Van said:
That sounds like your Regional Settings use semicolon as
the list separator, not comma.

Check the Regional Settings of your OS if you normally use
comma as the list separator.
Click to expand...

You're right.
Corrected with ; as separator.
very surprising for me !!!!
 
Back
Top