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
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