使用vba创建的此SQL是否存在错误? [英] Do I have an error in this SQL created using vba?
问题描述
我看不到为什么这个查询不起作用.我使用msgbox
来显示我的strSQL
,但对我来说看起来很好.
I cannot see why this query doesn't work. I use a msgbox
to display my strSQL
, but it looks perfectly fine to me.
Dim strSQL As String
strSQL = "INSERT INTO Jobs (Date, RefNo, ProjectID, PManager, PDesc, PONo, Src_Qty, WC_Qty, Rate, Total, Note, Company) "
strSQL = strSQL & "VALUES (" & JobDate.Value & ", '" _
& Text41.Value & "', '" _
& ProjectID.Value & "', '" _
& PManager.Value & "', '" _
& PDesc.Value & "', " _
& Text43.Value & ", " _
& Src_Qty.Value & ", " _
& WC_Qty.Value & ", " _
& newRate.Value & ", " _
& ProjTotal.Value & ", '" _
& Text38.Value & "', '" _
& newCompany.Value _
& "');"
MsgBox (strSQL)
DoCmd.RunSQL strSQL
推荐答案
添加一行:
Debug.Print strSQL
之后
MsgBox (strSQL)
转到立即窗口(Ctrl + g)并复制完成的SQL语句.然后创建一个新查询,切换到SQL View,然后粘贴在SQL语句中.弄清楚该语句失败的原因.我认为,如果Jobs表中的"Date"字段是Date/Time数据类型,那么klabranche就是钱.如果无法确定问题,请编辑问题以显示失败的SQL语句,并告诉我们Jobs字段的数据类型.
Go to the Immediate Window (Ctrl+g) and copy the completed SQL statement. Then create a new query, switch to SQL View, and paste in the SQL statement. Figure out why the statement fails. I think klabranche is on the money if the "Date" field in your Jobs table is Date/Time data type. If you're unable to identify the problem, edit your question to show the failing SQL statement, and tell us the data types of the Jobs fields.
此外,您的Jobs表包含3个字段,其名称为保留字:Date;速度;和注意. (请参阅 http://allenbrowne.com/AppIssueBadWord.html )如果保留这些字段名称,请在至少用方括号将其包围,以免混淆访问权限:
Also, your Jobs table includes 3 fields whose names are reserved words: Date; Rate; and Note. (See http://allenbrowne.com/AppIssueBadWord.html) If you keep those field names, at least surround them with square brackets to avoid confusing Access:
strSQL = "INSERT INTO Jobs ([Date], RefNo, ...
这篇关于使用vba创建的此SQL是否存在错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!