使用Dlookup更新查询 - 类型转换错误 [英] Update Query with Dlookup - Type Conversion Error
问题描述
我正在建立一个订单管理平台，并且从xls文件批量接收订单。 我已成功将文件导入新表
ImportTR ，但我无法从 ClientT 获取 ClientID > 要同步的表格。 ImportTR 表中的
Company_Name 将与
ClientT 表中的 CompanyName 相匹配所以我用它作为我的参考字段。
值得注意的数据库结构：
表格： ImportTR
- 字段： ClientID_FK （数字）
- 字段： Company_Name （短文）
表： ClientT
- 字段： ClientID （数字，否重复，主键）
- 字段： CompanyName （短文）
在更新查询中，我只有一个字段，设置如下：
字段： CompanyID_FK
表格：ImportTR
更新至： 使用DLookup（QUOT; [客户端ID] QUOT;，" ClientT"，" [公司名称] =" &安培; [ImportTR]！[Company_Name]）
标准：是空的
运行查询后，我收到以下错误消息，但不知道原因：
Microsoft Access无法更新所有记录在您的更新查询中。
由于类型转换失败，Microsoft Access未更新13个字段，由于密钥违规而导致0个记录，0记录（s）由于锁定违规而导致0条记录因违反验证规则而被记录。
您是否仍想继续运行此类操作查询？
To忽略错误并运行查询，单击是。
有关违规原因的说明，请单击"帮助"。
（帮助转到此路径：运行追加查询时的访问/查询/高级查询/常见错误）
我点击了是，并且没有g已更新。 我点击了帮助，它说明了可能存在冲突的原因，但我已经克服了所有可能性而无需任何更改（据我所知）。 正如你可能看到的那样，我好像在这里腌制
。 非常感谢您提供的任何帮助。
最后，目前只有13条记录需要更新，因为这是样本数据，但是日常使用会被缩放很多。
至于xls文件，我无法控制我收到它的方式，我知道我可能会添加一个带有vlookup的列来从另一个来源获取客户端ID但是我我试图尽可能地自动化，所以其他用户可以轻松地使用它。
谢谢。
我不认为您的DLookup（）正在检索任何值。我的猜测是返回空值，无法转换为数字;因此错误。
相反，请尝试以下查询：
UPDATE ImportTR INNER JOIN ClientT ON ImportTR.Company_Name = ClientT.CompanyName SET CompanyID_FK = ClientID
PS。不确定哪个是要更新的字段的正确名称，因为您在上面的表描述中将其称为ClientID_FK，但在查询说明中将其称为CompanyID_FK。
希望它有帮助...
Hi, I am building an order management platform and orders are received in batches from an xls file. I have been able to import the file successfully to a new table ImportTR, but I am unable to get the ClientID from the ClientT table to sync. Company_Name on the ImportTR table will match CompanyName from the ClientT table so I used that as my reference field.
Notable DB structure:
Table: ImportTR
- Field: ClientID_FK (Number)
- Field: Company_Name (Short Text)
Table: ClientT
- Field: ClientID (Number, No duplicates, Primary Key)
- Field: CompanyName (Short Text)
In the update query, I have one field only, setup as follows:
Field: CompanyID_FK
Table: ImportTR
Update To: DLookUp("[ClientID]","ClientT","[CompanyName]=" & [ImportTR]![Company_Name])
Criteria: Is Null
Upon running the query I receive the following error message and do not know why:
Microsoft Access can't update all the records in your update query.
Microsoft Access didn't update 13 field(s) due to a type conversion failure, 0 record(s) due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.
Do you want to continue running this type of action query anyway?
To ignore the error(s) and run the query, click Yes.
For an explanation of the causes of the violations, click Help.
(Help goes to this path: Access/Queries/Advanced queries/Common errors when you run an append query)
I have clicked Yes, and nothing is updated. I have clicked Help and it gave reasons that there might be a conflict but I got past all the possibilities without needing any changes (as far as I can tell). As you might see, I seem to be in a pickle
here. Any help you can provide is GREATLY appreciated.
Lastly, there is only 13 records to update at this time because that is sample data but day to day use will be scaled quite a bit.
And as for the xls file, I am unable to control how I receive it and I know I could likely add a column with a vlookup to get the client ID from another source but I am trying to automate as much as possible, so other users can use this with minimal trouble.
Thank you.
Hi,
I don't think your DLookup() is retrieving any value. My guess is it is returning Null values, which can't be converted into a Number; hence the error.
Instead, try the following query:
UPDATE ImportTR INNER JOIN ClientT ON ImportTR.Company_Name=ClientT.CompanyName SET CompanyID_FK=ClientID
PS. Not sure which is the correct name of the field to update because you called it ClientID_FK in your table description above but then called it CompanyID_FK in the query description.
Hope it helps...
这篇关于使用Dlookup更新查询 - 类型转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
