Solved Error 13 then 3421

[oxicottin]

Hello, I just cant figure out why im getting these errors! I know its a date field but don't know why. The data is getting pulled from a PLC and by looking at the code it gets converted into a date/time. I tried in the error handler to skip the Error 13 to see what it did and then it gives a error 3421 "Data type conversion error".

I do believe its this line that is causing it. The start and finish are date/time like 11/4/2020 00:51:00
DelayHours = RoundTo(DateDiff("n", plcDelayStart, plcDelayFinish) / 60, 2)

Anything I could try or suggestions would be great!! I been on this for two weeks now and cant figure it out. Thanks!

Public Sub line1()
    On Error GoTo line1_Err

    'Open the DDE channel to the PLC via the CTI driver
    ctiLink = DDEInitiate("CTI2572", "WV_PLC1")

    'Get the current date and time from the PLC
    plcTime = current505DateTime(ctiLink)
    'Display line thats recieving data
    Forms!frm_PLCDelayLogger.txtLineProcessing = "Line 1"

    Set db = CurrentDb

    'Set the starting address for each var
    alarmCodeAddress = 39500 '65000
    delayloggedaddress = 39500 '65000

    Do    'Loop through this code until all records are logged

        'Check the logged status to find out which record to log
        For delayToLog = 400 To 20 Step -10
            commCode = "V" & ((delayToLog) - 1) + delayloggedaddress
            'Debug.Print commCode
            If DDERequest(ctiLink, "V" & ((delayToLog) - 1) + delayloggedaddress) <> 1 Then
                Exit For
            End If
        Next delayToLog

        'If we have logged everything but the first record, gettem outta here
        If delayToLog = 10 Then Exit Do

        'Now we are going to use the record number to multiply _
         by the various PLC memory locations to go through the PLC "table"

        'Make the dates valid
        plcDelayStart = getDelayStartForRecord(ctiLink, delayToLog, delayloggedaddress)
        plcDelayFinish = getDelayFinishForRecord(ctiLink, delayToLog, delayloggedaddress)
        shift = getShiftForRecord(plcDelayStart)

        'Calculate the delay hours
        DelayHours = RoundTo(DateDiff("n", plcDelayStart, plcDelayFinish) / 60, 2)

        'Get the alarm code for this record
        AlarmCodeId = DDERequest(ctiLink, "V" & delayToLog + alarmCodeAddress)

        Set db = CurrentDb
        Set rstNewDelayRecord = db.OpenRecordset("tbl_Delay", dbOpenDynaset, dbSeeChanges)

        'Add the record to the table
        With rstNewDelayRecord
            .AddNew
            !TimeDelayBegin = plcDelayStart
            !TimeDelayEnd = plcDelayFinish
            !LineNumber = 1
            !DelayHours = DelayHours
            !ShiftID = shift
            !AlarmCodeId = AlarmCodeId
            .Update
        End With

        If Not rstNewDelayRecord Is Nothing Then rstNewDelayRecord.Close
        Set rstNewDelayRecord = Nothing

        Do
            tempaddress = "V" & ((delayToLog - 1) + delayloggedaddress)
            DDEPoke ctiLink, tempaddress, "1"
            'Debug.Print tempaddress
            'lets wait 5 seconds and try again
        Loop Until DDERequest(ctiLink, tempaddress) = 1

        'Display time line was logged
        Forms!frm_PLCDelayLogger.txtLastLogged = Now()

    Loop While delayToLog > 10

    'Cleanup CTI stuff
    DDETerminateAll

line1_Exit:
    Exit Sub
line1_Err:

    'Logs and displays error data
    Forms!frm_PLCDelayLogger.txtErrorProcessProc = "Public Sub line1()"
    Call LogError

    Resume line1_Exit
End Sub

 

[pbaldy]

Have you set a breakpoint and stepped through so you can examine the values? You can also comment out the "On Error..." line temporarily and hit Debug when you get the error. That will let you see the values too.

 

[oxicottin]

Yes and on that line:
DelayHours = RoundTo(DateDiff("n", plcDelayStart, plcDelayFinish) / 60, 2)

the values of the plcDelayStart was 00/00/200 and same for plcDelayFinish 00/00/200

 

[pbaldy]

I'm not at a computer right now but I don't think those are valid date/time values, and thus the function would error on them. What are those values supposed to represent?

 

[The_Doc_Man]

plcDelayStart = getDelayStartForRecord(ctiLink, delayToLog, delayloggedaddress)

I would put a breakpoint on that line, then single-step it and examine what came back. Either hovering the mouse or using the immediate window and the Debug.Print method would show you exactly what came back from there. I would single step again to see the ...Finish value. One of those two is going to be your culprit. I don't know what getShiftForRecord does, but it liked the ...Finish value so I am SLIGHTLY leaning towards the ...Start value being your bug-a-boo.

DelayHours = RoundTo(DateDiff("n", plcDelayStart, plcDelayFinish) / 60, 2)

It MIGHT also be a good idea to split that into two statements, capturing the minutes separately in a variable and then using the RoundTo function in a separate statement. But not for any other reason than trapping the problem by isolating all statements to their simplest form. There is nothing wrong syntactically with the 2nd statement, I just like to break things apart to be sure which part is grieving me.

 

[oxicottin]

I'm not at a computer right now but I don't think those are valid date/time values, and thus the function would error on them. What are those values supposed to represent?

Date and time.... Below is the function that pulls the Date and time for each record. I have no idea whats its doing or how to grab data from a PLC this was written years ago and has never worked Im trying to get it to work...

Public Function getDelayStartForRecord(ddeChannel As Variant, recordToLog As Integer, delayloggedaddress As String)
    On Error GoTo getDelayStartForRecord_Err

    Dim YYMM As Variant
    Dim YYYY As Variant
    Dim MM As Variant
    Dim DDHH As Variant
    Dim DD As Variant
    Dim HH As Variant
    Dim NNSS As Variant
    Dim NN As Variant
    Dim tempaddress As Variant

    tempaddress = delayloggedaddress + 1
    'Record 1 YYMM starts at 65001 and goes 10 for each record
    YYMM = DDERequest(ddeChannel, "V" & (tempaddress + ((recordToLog - 10)) & "B"))

    'Gotta add a leading zero if needed to make it a valid year
    If Len(YYMM) = 3 Then
        YYMM = "200" & YYMM
    Else
        YYMM = "20" & YYMM
    End If
    'Now we have 200105
    YYYY = Left(YYMM, 4)
    MM = Right(YYMM, 2)

    'Record 1 DDHH starts at 65002 and goes 10 for each record
    tempaddress = delayloggedaddress + 2
    DDHH = DDERequest(ddeChannel, "V" & (tempaddress + ((recordToLog - 10)) & "B"))

    If Len(DDHH) = 3 Then DDHH = "0" & DDHH
    'Now we have 1007
    DD = Left(DDHH, 2)
    HH = Right(DDHH, 2)

    'Record 1 MISS (minutes and seconds) starts at 65003 and goes 10 for each record
    'We don't care about seconds, we'll make em zero
    tempaddress = delayloggedaddress + 3
    NNSS = DDERequest(ddeChannel, "V" & (tempaddress + ((recordToLog - 10)) & "B"))
    If Nz(NNSS) = 0 Then
        'Get all zeros at the top of each hour
        NN = "00"
    Else
        If Len(NNSS) = 3 Then NNSS = "0" & NNSS
        NN = Left(NNSS, 2)
    End If

    getDelayStartForRecord = MM & "/" & DD & "/" & YYYY & "  " & HH & ":" & NN

getDelayStartForRecord_Exit:
    Exit Function
getDelayStartForRecord_Err:

    'Logs and displays error data
    Forms!frm_PLCDelayLogger.txtErrorProcessProc = "Public Function getDelayStartForRecord()"
    Call LogError

    Resume getDelayStartForRecord_Exit
End Function

Public Function getDelayFinishForRecord(ddeChannel As Variant, recordToLog As Integer, delayloggedaddress As String)
    On Error GoTo getDelayFinishForRecord_Err

    Dim YYMM As Variant
    Dim YYYY As Variant
    Dim MM As Variant
    Dim DDHH As Variant
    Dim DD As Variant
    Dim HH As Variant
    Dim NNSS As Variant
    Dim NN As Variant
    Dim tempendingaddress As Variant

    tempendingaddress = delayloggedaddress + 5
    'Record 1 YYMM starts at 65005 and goes 10 for each record
    YYMM = DDERequest(ddeChannel, "V" & (tempendingaddress + ((recordToLog - 10)) & "B"))

    'Gotta add a leading zero if needed to make it a valid year
    If Len(YYMM) = 3 Then
        YYMM = "200" & YYMM
    Else
        YYMM = "20" & YYMM
    End If
    'Now we have 200105
    YYYY = Left(YYMM, 4)
    MM = Right(YYMM, 2)

    'Record 1 DDHH starts at 65006 and goes 10 for each record
    tempendingaddress = delayloggedaddress + 6
    DDHH = DDERequest(ddeChannel, "V" & (tempendingaddress + ((recordToLog - 10)) & "B"))

    If Len(DDHH) = 3 Then DDHH = "0" & DDHH
    'Now we have 1007
    DD = Left(DDHH, 2)
    HH = Right(DDHH, 2)

    'Record 1 MISS (minutes and seconds) starts at 65007 and goes 10 for each record
    'We don't care about seconds, we'll make em zero
    tempendingaddress = delayloggedaddress + 7
    NNSS = DDERequest(ddeChannel, "V" & (tempendingaddress + ((recordToLog - 10)) & "B"))
    If Nz(NNSS) = 0 Then
        'Get all zeros at the top of each hour
        NN = "00"
    Else
        If Len(NNSS) = 3 Then NNSS = "0" & NNSS
        NN = Left(NNSS, 2)
    End If

    getDelayFinishForRecord = MM & "/" & DD & "/" & YYYY & "  " & HH & ":" & NN

getDelayFinishForRecord_Exit:
    Exit Function
getDelayFinishForRecord_Err:

    'Logs and displays error data
    Forms!frm_PLCDelayLogger.txtErrorProcessProc = "Public Function getDelayFinishForRecord()"
    Call LogError

    Resume getDelayFinishForRecord_Exit
End Function

 

[The_Doc_Man]

Our posts crossed. Those "00/00/200" values are NOT valid dates so DateDiff would have barfed.

 

[oxicottin]

Our posts crossed. Those "00/00/200" values are NOT valid dates so DateDiff would have barfed.

What could I use instead? Like I mentioned the dates are coming from a PLC which I have no control over and I have no idea how the functions work.

 

[oxicottin]

@The_Doc_Man I put break points at the line in question and opened the Locals Window and the image does indeed show the start date is 00/0/200 00:00

Suggestion's?

 

[Isaac]

and I have no idea how the functions work

This is an example of why don't just take people's advanced functions and use them if you don't understand and can't maintain them.

You must stop everything and thoroughly understand how those functions work before proceeding.

 

[arnelgp]

all your code depends on these variables YYMM, DDHH, NNSS since they all came from the DDERequest() function.
so you have to check the initial values of these variables when it got values from the function.

 

[oxicottin]

For one im the only one that somewhat knows a little about access and two the guy that wrote it left the company about 12 years ago. So with that being said i would love to know how the stat time funtion works. Throw me a bone if you have time....

Thanks!

 

[arnelgp]

maybe you could ask for any Tech manual of the PLC.
there is always a manual on how to interface the PLC software with MSA
and the 2 guys did managed to use it.

 

[oxicottin]

Well I did some poking around on that PC and its coming from Wonderware that Im guessing is grabbing it from the PLC. I know there is always a Intouch/Window viewer that is open that displays the start/stop ect times for each machine and its always correct. Any thoughts on how I get the info its grabbing OR should I post that question in a forum that is Wonderware based?

 

[The_Doc_Man]

Here is something I found easily on the web:

Wonderware - Wikipedia

en.wikipedia.org

You might just search the web for "Wonderware" and find the AVEVA link that could help you find manuals or a forum. From the articles I found, it would seem that Wonderware got bought out and has a new owner. It's a starting place, at least.