Error 3075 with Insert Into Statement.

B

bankinkid

Hi,

I am trying to insert data from a text file into an access table. I
am able to parse the data to exclude information that I do not want
(refer Select Case Statement), but when it goes to execute the Insert
Into Statement in stops with the error - "Syntax Error (missing
operator) in query expression '4812A0367'"

Any Ideas?

Code:

Private Sub ImporTxt()

Dim dbCurr As DAO.Database
Dim strCusip As String
Dim strDescription As String
Dim strMaturity As String
Dim strDays As String
Dim Price As String
Dim LongVal As String
Dim ShortVal As String
Dim strSP As String
Dim strMoody As String
Dim strFtch As String
Dim strDB As String
Dim strAMB As String
Dim strClassification As String
Dim intFile As String
Dim strBuffer As String
Dim strFilter As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()

strFile = "C:\Documents and Settings\Administrator\My Documents
\position_20090228.txt"
intFile = FreeFile()
Open strFile For Input As #intFile

Do While EOF(intFile) = False
Line Input #intFile, strBuffer
strFilter = Left(strBuffer, 4)
Select Case strFilter
Case "CUSI", " ", "FIRM", "ACCO", "TYPE", "CORP", "PAR ", "GOVT",
"COMM", "MUNI", "PREF", " ", ""
' This is of NO interest to you...
' Ignore
Case Else
strCusip = Mid(strBuffer, 1, 10)
strDescription = Mid(strBuffer, 11, 40)
strMaturity = Mid(strBuffer, 51, 12)
strDays = Mid(strBuffer, 63, 5)
Price = Mid(strBuffer, 68, 11)
LongVal = Mid(strBuffer, 96, 18)
ShortVal = Mid(strBuffer, 114, 19)
strSP = Mid(strBuffer, 134, 5)
strMoody = Mid(strBuffer, 139, 5)
strFtch = Mid(strBuffer, 144, 5)
strDB = Mid(strBuffer, 149, 5)
strAMB = Mid(strBuffer, 155, 5)
strClassification = Mid(strBuffer, 177, 24)
strSQL = "INSERT INTO Positions VALUES(" & strCusip & ", " &
strDescription & ", " & strMaturity & ", " & strDays & ", " & Price &
", " & LongVal & ", " & ShortVal & ", " & strSP & ", " & strMoody & ",
" & strFtch & ", " & strDB & ", " & strAMB & ", " & strClassification
& ")"
dbCurr.Execute strSQL, dbFailOnError
End Select
Loop

Close #intFile

Set dbCurr = Nothing

End Sub
 
D

Douglas J. Steele

You're best off specifying the specific fields in the INSERT INTO statement,
to guarantee they're in the same order as in the VALUES list:

You only need to put quotes around the text values: you don't put quotes
around numeric values. And if any of your fields are dates, you need to
delimit them with # characters (and they need to be either in mm/dd/yyyy
format, or some unambiguous format such as yyyy-mm-dd or dd mmm yyyy,
regardless of your Short Date format)

strSQL = "INSERT INTO Positions (Cusip, Description, " & _
"Maturity, Days, Price, LongVal, ShortVale, SP, Moody, Ftch, " & _
"DB, AMB, Classification) " &
"VALUES('" & strCusip & "', '" strDescription & "', " & _
strMaturity & ", " & strDays & ", " & Price & ", " & LongVal & _
", " & ShortVal & ", '" & strSP & "', '" & strMoody & "', '" & _
strFtch & "', '" & strDB & "', '" & strAMB & "', '" & _
strClassification & "')"




--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I am trying to insert data from a text file into an access table. I
am able to parse the data to exclude information that I do not want
(refer Select Case Statement), but when it goes to execute the Insert
Into Statement in stops with the error - "Syntax Error (missing
operator) in query expression '4812A0367'"

Any Ideas?

Code:

Private Sub ImporTxt()

Dim dbCurr As DAO.Database
Dim strCusip As String
Dim strDescription As String
Dim strMaturity As String
Dim strDays As String
Dim Price As String
Dim LongVal As String
Dim ShortVal As String
Dim strSP As String
Dim strMoody As String
Dim strFtch As String
Dim strDB As String
Dim strAMB As String
Dim strClassification As String
Dim intFile As String
Dim strBuffer As String
Dim strFilter As String
Dim strFile As String
Dim strSQL As String

Set dbCurr = CurrentDb()

strFile = "C:\Documents and Settings\Administrator\My Documents
\position_20090228.txt"
intFile = FreeFile()
Open strFile For Input As #intFile

Do While EOF(intFile) = False
Line Input #intFile, strBuffer
strFilter = Left(strBuffer, 4)
Select Case strFilter
Case "CUSI", " ", "FIRM", "ACCO", "TYPE", "CORP", "PAR ", "GOVT",
"COMM", "MUNI", "PREF", " ", ""
' This is of NO interest to you...
' Ignore
Case Else
strCusip = Mid(strBuffer, 1, 10)
strDescription = Mid(strBuffer, 11, 40)
strMaturity = Mid(strBuffer, 51, 12)
strDays = Mid(strBuffer, 63, 5)
Price = Mid(strBuffer, 68, 11)
LongVal = Mid(strBuffer, 96, 18)
ShortVal = Mid(strBuffer, 114, 19)
strSP = Mid(strBuffer, 134, 5)
strMoody = Mid(strBuffer, 139, 5)
strFtch = Mid(strBuffer, 144, 5)
strDB = Mid(strBuffer, 149, 5)
strAMB = Mid(strBuffer, 155, 5)
strClassification = Mid(strBuffer, 177, 24)
strSQL = "INSERT INTO Positions VALUES(" & strCusip & ", " &
strDescription & ", " & strMaturity & ", " & strDays & ", " & Price &
", " & LongVal & ", " & ShortVal & ", " & strSP & ", " & strMoody & ",
" & strFtch & ", " & strDB & ", " & strAMB & ", " & strClassification
& ")"
dbCurr.Execute strSQL, dbFailOnError
End Select
Loop

Close #intFile

Set dbCurr = Nothing

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

error in code 8
PLEASE HELP, insert 1
SQl query in VBA - SELECT inside an INSERT INTO 10
Converting Ole Objects containing linked addresses into hyperlinks 7
Help with browse button in Access 2010 0
error 3061 with insert into sql 6
Three days trying to debug a very simple INSERT INTO statement 2
insert into syntax error 4

Top