ACC: Imported Time Values Filtered Incorrectly

ID: Q189116

The information in this article applies to:

SYMPTOMS

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

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


  1. Create a text file that contains the following information: 
    
       04:30:00
       14:45:00
       05:55:00
       23:54:00


  2. Start Microsoft Access and create a new database.


  3. On the File Menu, point to Get External Data, and then click Import.


  4. From the Files Of Types box, select Text Files, and select the text file you created in step 1. Click Import.


  5. When the Import Text Wizard opens, click Finish.


  6. Open the table you just imported, and on the Records menu, point to Filter, and then click Filter By Form.


  7. Type 4:30 for Field1.


  8. On the Records menu, click Apply Filter/Sort.

    You will see one record with a time of 4:30 listed.


  9. On the Records Menu, point to Filter, and click Filter By Form.


  10. Change Field1 to 5:55.


  11. On the Records menu, click Apply Filter/Sort.

    You will see no records in the table.



REFERENCES

For more information about update queries, search the Help Index for "queries, action queries" and follow the link for Update Queries.

For more information about filtering a table by using filter by form, search the Help Index for "Filter By Form, creating."

Additional query words: pra 


Keywords          : kbdta CnvGnrl 
Version           : WINDOWS:2.0,7.0,97
Platform          : WINDOWS 
Issue type        : kbbug

Last Reviewed: April 27, 1999