Translate if statement in query

  • Thread starter Thread starter Jazz
  • Start date Start date
J

Jazz

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?
 
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
 
Back
Top