Prev: Currency Format Lost in Union Query
Next: Change colour of a field
From: Jazz on 27 May 2010 17:30
I took both of these if statements from a query in an access database and I
am trying to interpret them.

New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average
requested])

Old Request: IIf(IsNull([Demand_1]![Average
requested]),0,[Demand_1]![Average requested])

My interpretation is that each statement in the query is giving a field name
to the field and then doing something or nothing depending on the data in
TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you
help to help me translate these statements completely?

From: vanderghast on 29 May 2010 15:46
The name at the left of the : will be the name of the field in the result,
it is called an alias, and it is used when you care about it (else, you may
get a field name like Expression005 ).

As for the expression at the right of : , they both are on the model:

iif ( IsNull(fieldName), 0, fieldName )


If you read, in the help file, about iif, you would see that it takes 3
arguments:
test,
value to be returned if the test evaluates to true
value to be returned otherwise


So, in both case, if the field value is null, it returns 0 instead of the
null, else, it returns the value of the field.


Note that a NULL is not a ZERO. A Null is used when a value is unknown,
missing, not available, not appropiate, etc. while a zero is available,
right now, as it is sure the quantity is that, zero. A database is 'fishy'
in making a difference between null and zero. Sometimes, a null may be
appropriately considered as to be zero, but then, you use a formula like the
one you saw.



Vanderghast, Access MVP



"Jazz" <Jazz(a)discussions.microsoft.com> wrote in message
news:B130E952-A26D-471E-91BE-F3C67703B1C9(a)microsoft.com...
>I took both of these if statements from a query in an access database and I
> am trying to interpret them.
>
> New Request: IIf(IsNull([Demand]![Average requested]),0,[Demand]![Average
> requested])
>
> Old Request: IIf(IsNull([Demand_1]![Average
> requested]),0,[Demand_1]![Average requested])
>
> My interpretation is that each statement in the query is giving a field
> name
> to the field and then doing something or nothing depending on the data in
> TABLE: Demand / FIELD: Average requested, but I am not sure what. Are you
> help to help me translate these statements completely?
>

 | 
Pages: 1
Prev: Currency Format Lost in Union Query
Next: Change colour of a field