使用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屋！