Microsoft KB Archive/123491

= Works: Current Date Query Does Not Retrieve All Matching Records =

Article ID: 123491

Article Last Modified on 11/15/2004

-

APPLIES TO


 * Microsoft Works 2.0 Standard Edition
 * Microsoft Works 2.0a
 * Microsoft Works 3.0 Standard Edition
 * Microsoft Works 3.0a
 * Microsoft Works 3.0b
 * Microsoft Works 4.5 Standard Edition
 * Microsoft Works 4.5a
 * Microsoft Works 4.0 Standard Edition
 * Microsoft Works 4.0a

-



This article was previously published under Q123491



SYMPTOMS
When you query in the Database on records with the current date, Works may not display all matching records.



CAUSE
This will occur if the dates in the database itself were entered using different methods, for example, using the NOW function and manually typing them in.



RESOLUTION
To correctly obtain all matching records, regardless of how the date was entered, you must use a combination of the INT and NOW functions in the Query view. The correct query formula is:

INT=INT(NOW)

NOTES:

In Works 4.0, 4.5, or 4.5a, you can enter the formula as your filter by clicking Filter Using Formula in the Filter dialog box.

In Works 3.0, functions cannot be entered into the New Query dialog box. To enter a query formula into Query View, first click Create New Query from the Tools menu and then click Query View.

The above formula should work whether the dates have been entered into the database manually or as the result the =NOW function. One thing to be aware of, however, is that this formula will not work if the dates have been entered as text values rather than numbers--for example, by using dashes as separators instead of slashes.



MORE INFORMATION
Works stores dates as decimal numbers, where the integer portion of the number represents the day, month, and year, while the decimal portion of the date represents the hour, minute, and second. If you query on NOW, Works tries to match both the integer (date) and decimal (time) portions of the current date. Since the decimal or time portion is always changing, no records will be matched by the query. Thus, you need to truncate the value of NOW to the integer, or date, portion.

Additional query words: 2.00 2.00a 3.00 3.00a 3.00b 4.00 4.00a 4.50 4.50a w_works

Keywords: kbprb KB123491

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.