Query - Fields - Records

  • Thread starter Thread starter wisemax
  • Start date Start date
W

wisemax

A table having two records

BuildingName PurchaseDate PurchasePrice
East 1-1-2000 10,000,000
West 1-1-2001 12,000,000

A query having two fields

EastMonths:IIf([BuildingName]="East",DateDiff("m",[Forms]![Balance
Sheet Dialog]![EndingDate],[PurchaseDate])*-1)

WestMonths:IIf([BuildingName]="West",DateDiff("m",[Forms]![Balance
Sheet Dialog]![EndingDate],[PurchaseDate])*-1)


I want to calculate how many months since the purchase of the
buildings.

My question is why there are blank cells?

EastMonths WestMonths
18 blank
blank 30

Why the result does not look like
EastMonths WestMonths
18 30
 
A table having two records

BuildingName PurchaseDate PurchasePrice
East 1-1-2000 10,000,000
West 1-1-2001 12,000,000

A query having two fields

EastMonths:IIf([BuildingName]="East",DateDiff("m",[Forms]![Balance
Sheet Dialog]![EndingDate],[PurchaseDate])*-1)

WestMonths:IIf([BuildingName]="West",DateDiff("m",[Forms]![Balance
Sheet Dialog]![EndingDate],[PurchaseDate])*-1)


I want to calculate how many months since the purchase of the
buildings.

My question is why there are blank cells?

EastMonths WestMonths
18 blank
blank 30

Why the result does not look like
EastMonths WestMonths
18 30
Click to expand...

Because a query - with or without a function call such as IIf() -
returns one record for every record in the source table, unless you do
something to combine the records.

Thinking like a computer:

You have a record containing East and another record containing West.

The IIF statement has three arguments: a logical expression which
returns true or false; an answer to be returned if it's TRUE; and a
third field, an answer to be returned if it's FALSE.

EastMonths will return your DateDiff expression if it's TRUE; you
haven't specified what it should return if it's FALSE, so it will
return the DateDiff expression when for East, and will return NULL if
the value of the field is West (or anything other than East).

In other words - IIF isn't doing what you evidently think it should be
doing, but instead doing what it's designed and documented to do...
durn picky computers!


John W. Vinson[MVP]
 
Back
Top