Microsoft KB Archive/202130

= ACC2000: Imported Time Values Filtered Incorrectly =

Article ID: 202130

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q202130



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



SYMPTOMS
When you import a text file that contains time values into a Microsoft Access database, if you filter the table on specific time values, the filter may not return any records.



CAUSE
When you import a text file into a Microsoft Access database, if that file contains a date or a time value, a small, floating point math error occurs. When this error occurs, the number recorded for the date or time value during the import will be slightly different from the number recorded when you manually enter the same date or time value. When you try to filter on the Date/Time field, Microsoft Access will see these differences as not matching, even though the values appear to be identical.



RESOLUTION
You can correct this problem by running an update query against the imported table. This update query will use the Format function to first convert the Date/Time field value to a string, and then use the CVDate function to convert to a date or time data type. If for example, the Date/Time field is named MyDate, the update query would appear as follows:   Field: MyDate Update: CVDate(Format(MyDate, "hh:nn:ss am/pm"))



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.



Steps to Reproduce Behavior
 Create a text file that contains the following information:

04:30:00

14:45:00

05:55:00

23:54:00

 Start Microsoft Access and create a new database. On the File menu, point to Get External Data, and then click Import. In the Files Of Types box, select Text Files, and select the text file that you created in step 1. Click Import.</li> When the Import Text Wizard opens, click Finish. When prompted that the import was successful, click OK.</li> Open the table that you just imported, and on the Records menu, point to Filter, and then click Filter By Form.</li> Type 4:30 for Field1.</li> Click the Apply Filter/Sort tool on the command bar.

Note that you see one record with a time of 4:30 listed.</li> On the Records menu, point to Filter, and click Filter By Form.</li> Change Field1 to 5:55 .</li> Click the Apply Filter/Sort button on the command bar.

Note that you see no records in the table.</li></ol>

<div class="references_section">