MS Access 设置忽略日期转换错误
Access DB 导入固定宽度的文本文件；一列主要是日期。 当日期不可用时，文件的创建者实际上使用字符串“Null” Access 将行放入表中，该字段实际上为空。
但是，当文件开始具有不同的字段宽度时，我复制了数据库，调整了输入规范中的起始/宽度值，然后导入。现在，所有带有 null 的行都会作为将文本转换为日期的错误记录在 (table)_import_errors 中。
我没有找到任何设置（不是我更改任何设置）来解释它。一个区别是，虽然两个数据库都是 Access 2000 格式，但原始数据库位于仍然具有 Access 2000 的计算机上，而新数据库则由 Access 2003 处理。
这是 Access 版本中的行为更改吗？预处理文件是唯一的解决方案吗？
谢谢，大卫。如果它没有自行修复，我就会这么做（Excel 部分除外）。我发布了该内容，但显然有人不喜欢公开承认 Access 有错误。
唯一改变的是固定宽度纯文本输入中的两个其他列更宽。然而，Access “决定”连续三次尝试丢弃整行而不是仅丢弃日期字段。第四次，它仍然报告为错误，但导入了该行的其余部分。
因此，当 Access 无缘由地出现错误时，请重试一两次，然后尝试对文本转换进行显式编码。
An Access DB imports a fixed width text file; one column is mostly dates.
When the date is not available, the file's creator actually uses the string "Null"
Access puts the row in the table with that field actually null.
But, when the files started coming with different field widths, I copied the DB, tweaked the starting/width values in the input spec, and imported. NOW, all the rows with null get logging in (table)_import_errors as an error converting text to date.
I have found no setting (not that I changed any) to explain it. One difference is that although both DBs are in Access 2000 format, the original is on a machine that still has Access 2000, while the new one is being handled by Access 2003.
Is that a behavior change in the Access version? Is pre-processing the file the only solution?
Thanks, David. That's what I would have done (except for the Excel part) if it had not fixed itself. I posted that, but apparently someone didn't like the public admission that Access has bugs.
The only thing that changed was that two other columns in the fixed width plain text input was wider. Yet Access "decided" to discard the whole row instead of just the date field for three consecutive attempts. The fourth time, it still reported it as an error but imported the rest of the row.
So, when Access misbehaves for no good reason, try again a time or two, then try explicitly coding the conversion from text.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
两种可能性：
使用缓冲字段或缓冲表将日期字段导入到文本字段中。然后，您可以将其处理为最终目标字段中的适当值。
使用 SQL 导入而不是 DoCmd.TransferText。在这种情况下，您要做的就是在 FROM 子句中使用连接字符串，以便您可以处理 SELECT 中的日期字段：
将其转换为 INSERT 查询，然后就可以了。
Two possibilities:
Use a buffer field or buffer table that imports the date field into a text field. Then you can process that into the appropriate values in the final destination field.
Use a SQL import instead of DoCmd.TransferText. What you do in that case is use a connect string in the FROM clause so you can then process the date field in your SELECT:
Convert that into an INSERT query and you're golden.