Link to home
Start Free TrialLog in
Avatar of Karen Schaefer
Karen SchaeferFlag for United States of America

asked on

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

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

Avatar of jppinto
jppinto
Flag of Portugal image

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

Avatar of Karen Schaefer

ASKER

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

Avatar of rockiroads
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
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

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

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.
wouldn't I need to include the conversion strings used in the Values section of the Sql statement?

K
I mean to handle the Null values?

k
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 & "')"
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
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
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.
would you recommend I use the strsql string or convert my code to and Update recordset Like you first suggested?
EquipId = Text, PAmt is Number - Last serv date = date
tl-ftemview.png
I am getting an error:

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

got any ideas?

k
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
Still getting data type mismatch. - I even hard coded the SDD date - no luck
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')
just notice that some of the text contains commas - could this be cause the issue?

'BENCH,PWRD,TEST',
no because it is in quotes
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')"


back online tomorrow, late now so signing off, e-speak tomorrow, good nite
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
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
did u try using to_date around it? that it the way in oracle sql to insert dates
Yes, I did, however, it error - Access did not recognize it.
ASKER CERTIFIED SOLUTION
Avatar of rockiroads
rockiroads
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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.)
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.
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
help point me into the right direction - see my last comment for my workaround.

thanks for your assistance