Works: Current Date Query Does Not Retrieve All Matching Records (123491)
The information in this article applies to:
- Microsoft Works 2.0
- Microsoft Works 2.0a
- Microsoft Works 3.0
- Microsoft Works 3.0a
- Microsoft Works 3.0b
- Microsoft Works 4.5
- Microsoft Works 4.5a
- Microsoft Works for Windows 95, version 4.0
- Microsoft Works for Windows 95, version 4.0 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(<fieldname>)=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.
| Modification Type: | Major | Last Reviewed: | 11/15/2004 |
|---|
| Keywords: | kbprb KB123491 |
|---|
|