Works 3.0: Calculated IF Function Fields May Be Incorrect in Database (118538)


The information in this article applies to:

  • Microsoft Works 3.0
  • Microsoft Works 3.0a
  • Microsoft Works 3.0b
This article was previously published under Q118538

SYMPTOMS

Some values returned by an IF statement in a database field may be incorrect after printing or print previewing a sorted report.

This effect can also be seen without a report when the condition of the IF statement has a field that contains blanks compared to a string (for example, ' fieldname="Hello" ', ' fieldname<"Hi" ', and so on).

These symptoms usually occurs when all of the following conditions are true:
  • The report contains a field with an IF statement.
  • The report has been sorted.
  • The report has been viewed in print preview or has been printed.
  • A field used in the condition of the IF formula has some blank cells.

WORKAROUND

When this problem occurs, the calculated value will be wrong for the records where the field used in the condition of the IF formula is blank, or it is of the wrong type (for example, numeric instead of text).

If the field referenced in the IF formula is formatted incorrectly, change it to the correct type. If the field referenced by the IF formula has some blank cells, do one of the following:
  • Put data in all of the blank cells. -or-

  • Use the S or N formulas as shown below:

    If the field in the condition section of the IF function is a text field, apply the S function to the field name. For example:

    IF(S(fieldname)="hello",ValueIfTrue,ValueIfFalse)

    If the field in the condition section of the IF function is a Numeric field, apply the N function to the field name. For example:

    IF(N(fieldname)>512,ValueIfTrue,ValueIfFalse)

    In the above examples, "fieldname" is the name of the field used in the condition section of the IF function. "ValueIfTrue" is the value returned if the condition is true. "ValueIfFalse" is the value returned if the condition is false.

STATUS

Microsoft has confirmed this to be a problem in Works versions 3.0, 3.0a, and 3.0b for Windows. This problem no longer occurs in Microsoft Works 4.0 and later.

MORE INFORMATION

For more information about the IF statement, see the references section at the end of this article.

Steps to Reproduce Problem

  1. Create a new Database file in Works and switch to list view.
  2. Type 1 in the first record of the first field and press ENTER.
  3. Highlight the first column from record 1 to record 10.
  4. From the Edit menu, choose Fill Series then choose OK.
  5. Delete the entry in the first field of record 6.
  6. In the second field of the first record, type the following and press ENTER:

    =IF(Field1<5,"T","F")

    NOTE: This formula correctly returns "T" of "F" for all the records. Record 6 is "T" because blanks have a numeric value of zero.
  7. Create a new report with both the fields in the database.
  8. Sort the report on field1.
  9. Print Preview the report.
Notice that the formula is now returning "F" for the missing Record 6, which is incorrect.

REFERENCES

For more information about how to perform this task in Works, see your Works printed documentation or online Help.

"Microsoft Works for Windows User's Guide," version 3.0, pages 540-541, "IF(Condition, ValueIfTrue, ValueIfFalse)"
Modification Type:MajorLast Reviewed:9/26/2003
Keywords:kbbug KB118538
©2003 Microsoft Corporation. All rights reserved.