Geek asks and answers

Am I having an error in this SQL created using vba?

I do not understand why this request does not work. I use msgboxto display mine strSQL, but to me it looks great.

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
0
source share
5 answers

Add a line:

Debug.Print strSQL

after

MsgBox (strSQL)

Go to the Immediate window (Ctrl + g) and copy the completed SQL statement. Then create a new query, switch to SQL View and insert the SQL statement. Find out why the statement fails. I think klabranche is on the money if the Date field in your Jobs table is the Date / Time data type. If you cannot identify the problem, edit your question to show the failed SQL statement and let us know the data types of the Jobs fields.

, Jobs 3 , : Date; ; . (http://allenbrowne.com/AppIssueBadWord.html) , , , Access:

strSQL = "INSERT INTO Jobs ([Date], RefNo, ...
+3

, .

"VALUES (#" & JobDate.Value & "#, '" _

, .

+7

# , klabranche, . ISO yyyy-mm-dd, (Jobdate, "yyyy-mm-dd" ) . US # mm/dd/yyyy # format

: . ProjectID .

text43 test48 , , . , , , .

.Value.

DoCmd.RunSQL , . , . DAO, Currentdb.Execute strSQL, dbfailonerror.. ADO CurrentProject.Connection.Execute strCommand, lngRecordsAffected, adCmdText. docmd.setwarnings, .

docmd.setwarnings, , True . , . , , " ". , , MDB.

, . , currentdb.execute, , docmd.runsql . YMMV.

HansUp . -

+2

klabranche, , ( "#" ) . , CurrentDb.Execute DoCmd.RunSQL, , , . . , !

0

, , strSQL:

INSERT INTO Jobs (JobDate, RefNo, ProjectID, PManager, PDesc, PONo, Src_Qty, WC_Qty, JobRate, JobTotal, Note, Company) VALUES (# 2009-09-20 #, '1', '3', ' , "4", 2, 5, 6, 7, 42, "9", "" )

, .

iam get "Runtime Error: 3134, Syntax Error in INSERT INTO", regardless of Do.Cmd or CurrentDb.Execute

Thanks guys.

0
source

More articles:


All Articles