Microsoft KB Archive/248934

= ACC2000: Find and FindRecord Do Not Find Null Values in Some Data Types =

Article ID: 248934

Article Last Modified on 11/25/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q248934



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



SYMPTOMS
When you search for a Null value by using the Find command on the Edit menu, the Visual Basic for Applications equivalent of the Find command, or the FindRecord macro action, you may receive the following message:

Microsoft Access finished searching the records. The search item was not found.

You receive this message even though one or more records contain a Null value in the field being searched.



CAUSE
You may experience this problem when you are searching for Null in a field with a data type of Number, Date/Time, Currency, or OLE Object, and you are using one of the following methods to search:  The Find command on the Edit menu. The FindRecord Action in a macro.  Either of the following Visual Basic for Applications methods: DoCmd.DoMenuItem acFormBar, acEditMenu, 10,, acMenuVer70 DoCmd.RunCommand acCmdFind 



RESOLUTION
To resolve this problem, obtain Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a).

To obtain SR-1/SR-1a, click the article number below to view the article in the Microsoft Knowledge Base:

245025 OFF2000: How to Obtain and Install Microsoft Office 2000 Service Release 1/1a (SR-1/SR-1a)

To temporarily work around this problem, use either of the following methods, which are explained in detail in the "More Information" section later in this article:
 * Filter by Form
 * The FindFirst Method



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Office 2000 SR-1/SR-1a.



MORE INFORMATION
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

To Use Filter by Form

 * 1) Open a table in Datasheet view or a form in Form View.
 * 2) On the Records menu, click Filter, and then click Filter by Form.
 * 3) Select the appropriate field and type Is Null.
 * 4) On the Filter menu, click Apply Filter/Sort.

To Find the Record with the FindFirst method
 Open the sample database Northwind.mdb.</li> Open the Employees form in Design view.</li> Create a command button, and then set its properties as follows:

Name: cmdFind

Caption: Find the Null value

OnClick: [Event Procedure]

</li> On the View menu, click Code.</li> On the Tools menu, click References.</li> Locate and click to select the Microsoft DAO 3.6 Object Library check box.</li>  Set the OnClick property of the command button to the following event procedure: Private Sub cmdFind_Click Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "IsNull([HireDate])" Me.Bookmark = rs.Bookmark End Sub </li> Save the Employees form.</li> Follow the first three steps in the "Steps to Reproduce Behavior" section.</li> Open the Employees form in Form view, and then click the Find the Null value command button.

Note that the form displays the record for Anne Dodsworth.</li></ol>

Steps to Reproduce Behavior
<ol> Open the sample database Northwind.mdb.</li> Open the Employees table in Datasheet view.</li> Delete the value from the Hire Date field of the last record (that of Anne Dodsworth).</li> Select the Hire Date field of any other record.</li> On the Edit menu, click Find, and in the Find What box type Null ; then, click Find Next.

Note that you receive the following message:

Microsoft Access finished searching the records. The search item was not found.

</li></ol>

Additional query words: pra data type can't cannot locate

Keywords: kbbug kbpending KB248934

-

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

© Microsoft Corporation. All rights reserved.