Microsoft KB Archive/189116

= ACC: Imported Time Values Filtered Incorrectly =

Article ID: 189116

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q189116



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 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
This problem can be corrected by running an update query against the imported table that uses 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 this to be a problem in the Microsoft products listed at the beginning of this article.



MORE INFORMATION
NOTE: This problem has also been found to occur when you import a Microsoft Access 2.0 table containing Date/Time fields into either Microsoft Access 7.0 or 97.

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. From the Files Of Types box, select Text Files, and select the text file you created in step 1. Click Import.</li> When the Import Text Wizard opens, click Finish.</li> Open the table you just imported, and on the Records menu, point to Filter, and then click Filter By Form.</li> Type 4:30 for Field1.</li> On the Records menu, click Apply Filter/Sort.

You will 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> On the Records menu, click Apply Filter/Sort.

You will see no records in the table.</li></ol>

<div class="references_section">