Microsoft KB Archive/67251

Querying on Date Fields in Database Containing Blank Fields

PSS ID Number: Q67251 Article last modified on 10-16-1998

2.x 3.00

MS-DOS

=
========================================================= 2.x 3.00 MS-DOS kbother

= SUMMARY =

If you query on a date field in a database to search for records that are “less than” (<) a specific date, Works will not only bring up the records requested by your query, but it will also bring up all records containing blanks in the date field. For example, if you enter

<=‘1/1/70’

to search for all records on or before 1/1/70, the query will produce all records on or before 1/1/70 and any record containing a blank in the date field as well.

A workaround is to search for nonblank (<>) records and the date requested.

Example
Works 2.0:

<>0&<=‘1/1/70’

Works 3.0:

<>0#AND#<=‘1/1/70’

Blank date fields are considered 0 (zero) and will be considered less than (<) the date requested. Therefore, a search for nonblank fields (<>0) is needed in order to search for any date involving a single less than (<) date request.

KBCategory: kbother KBSubcategory: dworkskb

= Additional reference words: 2.00 3.00 =

Version : 2.x 3.00 Platform : MS-DOS ============================================================================= Copyright Microsoft Corporation 1998.