Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Error -2147217900 (Syntax error in INSERT INTO statement.)

Posted on 2009-04-07
33
Medium Priority
?
492 Views
Last Modified: 2013-12-19
I am getting an  error msg - possible issue with the Insert Into portion of the statement.

I am using a Oracle connection string to access oracle data and want to update/append to an Access table.

what am I missing?  Why can't I use the Insert Into in this context?

k
       strsql = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,"
        strsql = strsql & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,"
        strsql = strsql & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model,"
        strsql = strsql & "VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE"
        strsql = strsql & "VALUES('"
        strsql = strsql & Nz(adoRS("FirstofEquipment_ID"), "") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '"""
        strsql = strsql & varNM & "', '" & varEQL & "', '"""
        strsql = strsql & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "', '"
        strsql = strsql & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "', '"
        strsql = strsql & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
        strsql = strsql & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
        strsql = strsql & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & varPDate & "')"
    
    DoEvents
    Debug.Print strsql
        CurrentProject.Connection.Execute strsql, dbFailOnError
 
DIES HERE AFTER THE ATTEMPT TO CONNECTION THE SQL STRING
 
 
results of query:
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model,VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH,PWRD,TEST', '"WORK BENCH', 'RG TRLR', '"4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '')658', '658', '658')3/1/1967', '3/1/1967', '3/1/1967')

Open in new window

0
Comment
Question by:Karen Schaefer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 19
  • 12
  • 2
33 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 24091143
You have several type mistakes on your SQL INSERT statment like ) instead of '. The code below it's OK for you yo try it.

jppinto
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH,PWRD,TEST', '"WORK BENCH', 'RG TRLR', '4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '', '658', '658', '658','3/1/1967', '3/1/1967', '3/1/1967')

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 24091168
Could you please update the code instead of the actual query statement results, Not sure where to make the changes in my code.

thanks for the assist.

K
0
 
LVL 33

Expert Comment

by:jppinto
ID: 24091170
Sorry, still found 6 more errors on my code! Mainly ' missing! I think that this is OK now.
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, 
SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID,SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATEVALUES('BC 336493', 'BENCH' ,'PWRD', 'TEST', 'WORK BENCH', 'RG TRLR', '4212', '1', '', '', 'DUPAR DYN', 'SPEC', '', '', '658', '658', '658', '3/1/1967', '3/1/1967', '3/1/1967')
 

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24091175
Having used a database to access Oracle before, I used one of two ways

1. Linked tables
2. Using ODBC, create ADO connection

Im assuming here you have linked tables?

Looking at your sql statement, you may have missing values, I know you used NZ but you would need to give a number for any numeric fields as default value.

You are dumping the generatd sql in the immediate window

debug.print strSQL

I suggest you look at that sql, paste it here, I can have a look and also run it in a new query to see what it is complaining about
0
 

Author Comment

by:Karen Schaefer
ID: 24091209
I am using a DSN-less connection string - here is my entire code - it works find until I try to update the Access table TL_FTEM_Temp.

Thanks for the assist.

K
Public Function FTCSConnection()
    Dim sConn As String
    Dim oConn As ADODB.Connection
    Dim rstOra As ADODB.Recordset, rs As ADODB.Recordset
    Dim adoRS As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim ctl As Control
    Dim j, I As Long
    Dim rsField, tblField As String
    Dim rsValue, tblValue As String
    Dim varNM As Variant  ' Nomenclature_Modifier
    Dim varLSD As Variant ' Last Service Date
    Dim varRNG As Variant ' Range
    Dim varSDD As Variant ' Service Due Date Cmt
    Dim varEQL As Variant ' Equipment Location
    Dim varPAmt As Variant ' PURCHASE_AMT
    Dim varPDate As Variant ' PURCHASE_DATE
   On Error GoTo FTCSConnection_Error
 
    DoCmd.SetWarnings False
 
    Set cn = CurrentProject.Connection
 
    sConn = _
        "Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" & _
        "(Host=Server1.com)(Port=1521)))(CONNECT_DATA=(SID=serv1)));" & _
        "User Id=*****;Password=******"
    Set adoConn = New ADODB.Connection
    adoConn.Open sConn
    Set adoRS = New ADODB.Recordset
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT varchar"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE varchar"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Delete * from TL_FTEM_TEMP"
        CurrentProject.Connection.Execute STRSQL
    Set rs = New ADODB.Recordset
    STRSQL = "Select * from TL_FTEM_TEMP"
    rs.Open STRSQL, cn, adOpenDynamic, adLockOptimistic
 
    STRSQL = " SELECT DISTINCT A.FTEM_ID AS FirstofEquipment_ID, A.EQPT_NAME AS NOMENCLATURE, A.NOMEN_MODIFIER_NAME AS Nomenclature_Modifier," & _
                    " A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE,COUNT(*) AS CountOfEQUIPMENT_ID," & _
                " A.SERVICE_DUE_DATE_CMT, A.LAST_SERVICE_DATE, A.MFR_NAME AS Manufacturer, A.MFR_NO AS Model, A.PART_VENDOR_SERIAL_NO AS VendorPart, A.RANGE," & _
                " EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
                " FROM Server1.FTEM_VI_VW AS A INNER JOIN Server1.EQUIPMENT_MANAGEMENT AS EM ON A.FTEM_ID = EM.FTEM_ID" & _
                " GROUP BY A.FTEM_ID, A.EQPT_NAME, A.NOMEN_MODIFIER_NAME, A.EQPT_LOC_NO, A.SERVICE_ORGN_CODE," & _
                    " A.Service_Due_Date_CMT," & _
                    " A.LAST_SERVICE_DATE, A.MFR_NAME, A.MFR_NO, A.PART_VENDOR_SERIAL_NO, A.RANGE," & _
                    " EM.PURCHASE_AMT, EM.PURCHASE_DATE" & _
                " HAVING (((A.SERVICE_ORGN_CODE) Is Not Null))" & _
                " ORDER BY A.FTEM_ID;"
                Debug.Print STRSQL
    Set adoRS = New ADODB.Recordset
    adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly
    adoRS.MoveFirst
    Do Until adoRS.EOF
        If Not IsNull(adoRS("Nomenclature_Modifier")) And adoRS("Nomenclature_Modifier") <> "" Then
            varNM = Replace(adoRS("Nomenclature_Modifier"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varNM = Replace(adoRS("Nomenclature_Modifier"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") <> "" Then
            varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS("SERVICE_DUE_DATE_CMT") <> "" Then
            varSDD = Format(DateSerial(Left(adoRS("SERVICE_DUE_DATE_CMT"), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3, 2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
        Else
            varSDD = adoRS("SERVICE_DUE_DATE_CMT")
        End If
        Debug.Print varSDD
        If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS("LAST_SERVICE_DATE") <> "" Then
            varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short Date")
        Else
            varLSD = adoRS("LAST_SERVICE_DATE")
        End If
        If Nz(adoRS("Range"), "") <> "" Then
            varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
            varRNG = LTrim(varRNG)
        End If
        If Not IsNull(adoRS("PURCHASE_AMT")) And adoRS("PURCHASE_AMT") <> "" Then
           varPAmt = Replace(adoRS("PURCHASE_AMT"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
           varPAmt = Replace(adoRS("PURCHASE_AMT"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("PURCHASE_DATE")) And adoRS("PURCHASE_DATE") <> "" Then
            varPDate = Format(CDate(adoRS("PURCHASE_DATE")), "Short Date")
        Else
            varPDate = adoRS("PURCHASE_DATE")
        End If
        STRSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        STRSQL = STRSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        STRSQL = STRSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, "
        STRSQL = STRSQL & "VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE" & _
        STRSQL = STRSQL & "VALUES('"
        STRSQL = STRSQL & Nz(adoRS("FirstofEquipment_ID"), "") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '"
        STRSQL = STRSQL & varNM & "', '" & varEQL & "', '"
        STRSQL = STRSQL & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "', '"
        STRSQL = STRSQL & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "', '"
        STRSQL = STRSQL & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
        STRSQL = STRSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
        STRSQL = STRSQL & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "', '" & varPDate & "')"
    
    DoEvents
        CurrentProject.Connection.Execute STRSQL, dbFailOnError
        adoRS.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    adoRS.Close
    Set adoRS = Nothing
    
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column SERVICE_DUE_DATE_CMT Date"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column LAST_SERVICE_DATE Date"
         CurrentProject.Connection.Execute STRSQL
    STRSQL = "Alter Table [TL_FTEM_TEMP] alter column PURCHASE_DATE Date"
         CurrentProject.Connection.Execute STRSQL
    DoCmd.Close acTable, "TL_FTEM_TEMP", acSaveYes
   On Error GoTo 0
   Exit Function
 
FTCSConnection_Error:
 
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FTCSConnection of Module Functions"
     
End Function

Open in new window

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091215
Another way to add a record, which will definitely make life easier for you, is to create a recordset to add. I say this due to the large number of fields you have

dim rsAdd as new adodb.recordset

set rsAdd = currentdb.openrecordset("TL_FTEM_Temp")
rsAdd.AddNew

'Set fields
rsAdd!FirstofEquipment_ID = adoRS!FirstofEquipment_ID
rs!NOMENCLATURE = adoRS!NOMENCLATURE
rs!Nomenclature_Modifier = varNM
'etc for all your other values

rsAdd.Update

0
 

Author Comment

by:Karen Schaefer
ID: 24091258
How would I handle the conversion of the data types - I would prefer not to use the Alter Table statements if I don't have to.  - especially on the Service_Due_Date_CMt field - that comes across as a number ie. 090824  needs to be converted and stored as a Short date - Problem I am having is the conversion from Oracle to Access.
0
 

Author Comment

by:Karen Schaefer
ID: 24091316
wouldn't I need to include the conversion strings used in the Values section of the Sql statement?

K
0
 

Author Comment

by:Karen Schaefer
ID: 24091328
I mean to handle the Null values?

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091396
looking at your sql

you got so many payments, yet just one payment in the field list

        strSQL = strSQL & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & Nz(adoRS("PURCHASE_AMT"), "") & "', '" & varPAmt & "')"
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091466
yes, put your NZ in as usual
Im not sure if a date conversion will be done but the good thing about using recordsets is you dont have to worry about datatype.

In your sql, I split it up (not runnable) so you can verify if the value is right for the field

        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & ", '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
       
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "',"
       
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
       
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE"
        strSQL = strSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "',"

        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "')"
       

If all looks right, put it in the right order
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091490
here it is merged
        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & ", '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & Nz(adoRS("CountOfEQUIPMENT_ID"), "") & "',"
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"


question, is the firstequipmentid a number or text?
same question to payment amount  (this has been put into quotes, if number, do u really want quotes?)

with the dates, not sure if you have to use to_date on it
0
 

Author Comment

by:Karen Schaefer
ID: 24091500
However, I need to convert Date for Service_Due_Date_cmt - this is not formatted as s date in the Oracle data - it is formatted ie.  090824 or not a Date Serial - hence the need to convert the date and format as a short date.
0
 

Author Comment

by:Karen Schaefer
ID: 24091503
would you recommend I use the strsql string or convert my code to and Update recordset Like you first suggested?
0
 

Author Comment

by:Karen Schaefer
ID: 24091538
EquipId = Text, PAmt is Number - Last serv date = date
tl-ftemview.png
0
 

Author Comment

by:Karen Schaefer
ID: 24091654
I am getting an error:

Error -2147217913 (Data type mismatch in criteria expression.)

got any ideas?

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24091806
ok, this handles the numbers. We have removd single quotes


strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', " & adoRS("CountOfEQUIPMENT_ID") & ","
        strSQL = strSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & varPAmt & ", '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"


date is in YYMMDD format

recommend - well either would do, sql insert or recordset, for large number of fields, I prefer the lattr just cos it makes maintenence easier

as a test, can you hardcode the dates, at the very least we know all we got left ot sort out is the dates
0
 

Author Comment

by:Karen Schaefer
ID: 24091857
Still getting data type mismatch. - I even hard coded the SDD date - no luck
0
 

Author Comment

by:Karen Schaefer
ID: 24091866
INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, Model, VendorPart, RANGE, PURCHASE_AMT, PURCHASE_DATE)  VALUES ('BC 336493', 'BENCH,PWRD,TEST', 'WORK BENCH','RG TRLR', '4212', 1,'12/1/2009', '', 'DUPAR DYN','BOEING SPEC', '', '',658, '3/1/1967')
0
 

Author Comment

by:Karen Schaefer
ID: 24091885
just notice that some of the text contains commas - could this be cause the issue?

'BENCH,PWRD,TEST',
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092019
no because it is in quotes
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092073
can u try this, wrap dates with to_date
dunno if it will work with date mask YYMMDD, might need to change it around

in your hardcoded test, change the date to be like htis

from

12/1/2009

to

TO_DATE('12/1/2009','MM/DD/YYYY')




if it works, try running it with this updated query

        strSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier, "
        strSQL = strSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        strSQL = strSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, Manufacturer, "
        strSQL = strSQL & "Model, VendorPart, RANGE, "
        strSQL = strSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        strSQL = strSQL & " VALUES ('"
        strSQL = strSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "',"
        strSQL = strSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', " & adoRS("CountOfEQUIPMENT_ID") & ","
        strSQL = strSQL & "to_date('" & varSDD & "','YYMMDD'), '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        strSQL = strSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        strSQL = strSQL & varPAmt & ", to_date('" & adoRS("PURCHASE_DATE") & "','YYMMDD')"


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24092156
back online tomorrow, late now so signing off, e-speak tomorrow, good nite
0
 

Author Comment

by:Karen Schaefer
ID: 24092177
goodnite- I am stepping thru each field to see which one is causeing the problem - so far not finding where the issue lies - thanks for the assist.

k
0
 

Author Comment

by:Karen Schaefer
ID: 24092471
After stepping thru the sql string - it is definitely the dates that are causing the problems - when I remove them from the string - the code works correctly.  Need to be able to input the dates either as is and convert before updating the final table(linked) or find a way to convert the dates.

k
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24094392
did u try using to_date around it? that it the way in oracle sql to insert dates
0
 

Author Comment

by:Karen Schaefer
ID: 24097724
Yes, I did, however, it error - Access did not recognize it.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 2000 total points
ID: 24099060
its gotta be inside the string, not outside as it is not a access function. is that what u did?

so in your hardcoded test
where you have in quotes

"12/1/2009"

change to

"TO_DATE('12/1/2009','MM/DD/YYYY')"

if u having difficulties with your hardcoded test, paste it here then I will change it for you. I need to confirm if this is what we need to do

0
 

Author Comment

by:Karen Schaefer
ID: 24099312
got the following error:

Error -2147217900 (Syntax error (missing operator) in query expression ''TO_DATE('12/1/2009','MM/DD/YYYY')''.)

debug results:  TO_DATE('12/1/2009','MM/DD/YYYY')

I even tried to just past the dates as is into the temp table then using an Access query try to convert the text into dates using the following syntax - however got #Error in the empty cells - I played with the ISEmpty, ISNull - both still return the #Error on the what appears to be empty fields.

vcDueDate: IIf(IsNull([SERVICE_DUE_DATE_CMT]),"Hello",Format(DateSerial(Left([SERVICE_DUE_DATE_CMT],2),Mid([SERVICE_DUE_DATE_CMT],3,2),Right([SERVICE_DUE_DATE_CMT],2)),"Short Date"))

Or

Test: CDate([SERVICE_DUE_DATE_CMT])

    adoRS.Open STRSQL, adoConn, adOpenDynamic, adLockReadOnly
    adoRS.MoveFirst
    Do Until adoRS.EOF
        If Not IsNull(adoRS("Nomenclature_Modifier")) And adoRS("Nomenclature_Modifier") <> "" Then
            varNM = Replace(adoRS("Nomenclature_Modifier"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varNM = Replace(adoRS("Nomenclature_Modifier"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("EQPT_LOC_NO")) And adoRS("EQPT_LOC_NO") <> "" Then
            varEQL = Replace(adoRS("EQPT_LOC_NO"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varEQL = Replace(adoRS("EQPT_LOC_NO"), "'", "''") 'double up single quotes
        End If
        'If Not IsNull(adoRS("SERVICE_DUE_DATE_CMT")) And adoRS("SERVICE_DUE_DATE_CMT") <> "" Then
            varSDD = "TO_DATE('12/1/2009','MM/DD/YYYY')"
          '  varSDD = Format(DateSerial(Left(adoRS("SERVICE_DUE_DATE_CMT"), 2), Mid(adoRS("SERVICE_DUE_DATE_CMT"), 3, 2), Right(adoRS("SERVICE_DUE_DATE_CMT"), 2)), "Short Date")
       Debug.Print varSDD
       ' Else
            'varSDD = adoRS("SERVICE_DUE_DATE_CMT")
        'End If
        If Not IsNull(adoRS("LAST_SERVICE_DATE")) And adoRS("LAST_SERVICE_DATE") <> "" Then
           ' varLSD = Format(CDate(adoRS("LAST_SERVICE_DATE")), "Short Date")
        'Else
            varLSD = adoRS("LAST_SERVICE_DATE")
        End If
        If Nz(adoRS("Range"), "") <> "" Then
            varRNG = Replace(adoRS("Range"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varRNG = Replace(adoRS("Range"), "'", "''") 'double up single quotes
            varRNG = LTrim(varRNG)
        End If
        If Not IsNull(adoRS("PURCHASE_AMT")) And adoRS("PURCHASE_AMT") <> "" Then
            varPAmt = Replace(adoRS("PURCHASE_AMT"), Chr(34), Chr(34) & Chr(34)) 'double up double quotes
            varPAmt = Replace(adoRS("PURCHASE_AMT"), "'", "''") 'double up single quotes
        End If
        If Not IsNull(adoRS("PURCHASE_DATE")) And adoRS("PURCHASE_DATE") <> "" Then
            varPDate = adoRS("PURCHASE_DATE")
        End If
      
        STRSQL = "INSERT INTO TL_FTEM_Temp (FirstofEquipment_ID, NOMENCLATURE, Nomenclature_Modifier,"
        STRSQL = STRSQL & "EQPT_LOC_NO, SERVICE_ORGN_CODE, CountOfEQUIPMENT_ID, "
        STRSQL = STRSQL & "SERVICE_DUE_DATE_CMT, LAST_SERVICE_DATE, "
        STRSQL = STRSQL & "Manufacturer, "
        STRSQL = STRSQL & "Model, VendorPart, RANGE, "
        STRSQL = STRSQL & "PURCHASE_AMT, PURCHASE_DATE) "
        STRSQL = STRSQL & " VALUES ('"
        STRSQL = STRSQL & adoRS("FirstofEquipment_ID") & "', '" & Nz(adoRS("NOMENCLATURE"), "") & "', '" & varNM & "'," ',"
        STRSQL = STRSQL & "'" & varEQL & "', '" & Nz(adoRS("SERVICE_ORGN_CODE"), "") & "', '" & adoRS("CountOfEQUIPMENT_ID") & "',"
        STRSQL = STRSQL & "'" & varSDD & "', '" & varLSD & "', '" & Nz(adoRS("Manufacturer"), "") & "',"
        STRSQL = STRSQL & "'" & Nz(adoRS("Model"), "") & "', '" & Nz(adoRS("VendorPart"), "") & "', '" & varRNG & "',"
        STRSQL = STRSQL & "'" & varPAmt & "', '" & Nz(adoRS("PURCHASE_DATE"), "") & "')"
    
    DoEvents
        CurrentProject.Connection.Execute STRSQL, dbFailOnError
        adoRS.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    adoRS.Close
    Set adoRS = Nothing
 
'         CurrentProject.Connection.Execute STRSQL
    DoCmd.Close acTable, "TL_FTEM_TEMP", acSaveYes

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 24099468
also getting on the SvcDueDateCmt field when there s an actual data - 2/19/1999

Error -2147217833 (The field is too small to accept the amount of data you attempted to add.  Try inserting or pasting less data.)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24099848
sorry, got busy with somthing

right

I see what u did with the date, you still got the payment date to change but if varsDD aint working then thats an issue. What is the datatype of this in the oracle table? fact that it doesnt take a date is worrying. I know its a date in Access but just confirm the field datatype in oracle.

I dont know if u got any tools, like TOAD or SQL*Plus to do it.
0
 

Author Comment

by:Karen Schaefer
ID: 24102321
solved the problem by using the Alter table function to change the field types to varchar then changing it back on the end of running the update/append queries.  the changes where made to the temp table, then used another append query to update the linked table source.  and this seems to work great.

thanks for all your input.

Karen
0
 

Author Closing Comment

by:Karen Schaefer
ID: 31567703
help point me into the right direction - see my last comment for my workaround.

thanks for your assistance
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question