ACC2000: Imported Time Values Filtered Incorrectly

• Microsoft Access 2000

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 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 this to be a problem in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

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. In the Files Of Types box, select Text Files, and select the text file that you created in step 1. Click Import.



5. When the Import Text Wizard opens, click Finish. When prompted that the import was successful, click OK.



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



7. Type 4:30 for Field1.



8. Click the Apply Filter/Sort tool on the command bar.
   
   Note that you 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. Click the Apply Filter/Sort button on the command bar.
    
    Note that you see no records in the table.

REFERENCES

For more information about update queries, click Microsoft Access Help on the Help menu, type "creat an action query" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

For more information about Filter by Form, click Microsoft Access Help on the Help menu, type "filter form blank" in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: pra

Keywords : kbdta CnvGnrl
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug