Getting extra characters in column (1 Viewer)

[lution]

Weird behavior copying a column from one record to another. Code is:

                Set fld = Records.Fields("violationLocalOrdinanceNumber")
                If Len(fld & "") > 0 Then
                    rst!LOR_NB = Nz(Records!violationLocalOrdinanceNumber, "")
                    Debug.Print ("LOR NB:" & rst!LOR_NB & "." & " len:" & Len(Nz(Records!violationLocalOrdinanceNumber, "")) & " len new:" & Len(rst!LOR_NB))
                End If

Some of the output I'm getting is:
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25

No matter what the original string length is, something is adding extra spaces and forcing it to 25 in the new record. This is the only field I've been able to identify with this issue. Column definition is char(25), no indexes or anything special that I can tell.

Any ideas? Tried adding a left() function call after the assignment but that didn't help either.

-Lution

 

[JHB]

You're not update the recordset after you apply a value to it, maybe either you're a setting it in edit mode, I can't see it from your code, but you can have done above the code you show!
The normally way to apply some value to a recordset:

  rst.Edit
  rst![Fieldname]=SomeValue
  rst.Update

 

[lution]

JHB,

I just included that snippet since the function is so long. Here is the complete code. The only field that I've found fills the full character length is LOR_NB.:

Private Sub TransformandAddv10(Records As DAO.Recordset, formType As String, Optional UsingSQL As Boolean = False)
    ' Comments:
    ' Params  : Records
    '           FormType
    '           UsingSQL
    ' Modified:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
Dim bIsParking As Boolean

On Error Resume Next

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("tblTraCSCitation")

    If Not Records.EOF And Not Records.BOF Then

        Records.MoveFirst
        Do While Not Records.EOF

            rst.AddNew

                rst!bSynchronized = False
                rst!dReadDate = Date
                
                bIsParking = False
                Set fld = Records.Fields("documentParkingTicketFlag")
                If Len(fld & "") > 0 Then
                    If (Records!documentParkingTicketFlag = "Y") Then bIsParking = True
                End If

                Set fld = Records.Fields("documentWisconsinFormVersion")
                If Len(fld & "") > 0 Then
                    rst!FORM_VERS_CD = Nz(Records!documentWisconsinFormVersion, "")
                End If

                Set fld = Records.Fields("documentDocumentNumber")
                If Len(fld & "") > 0 Then
                    rst!UTC_NB = Nz(Records!documentDocumentNumber, "")
                Else
                    rst!UTC_NB = vbNull
                End If
                
                Set fld = Records.Fields("documentCheckDigit")
                If Len(fld & "") > 0 Then
                    rst!UTC_NB_CK_DGT = Nz(Records!documentCheckDigit, "")
                Else
                    rst!UTC_NB_CK_DGT = vbNull
                End If

                Set fld = Records.Fields("documentWisconsinFormRevisionDate")
                If Len(fld & "") > 0 Then
                    rst!VERS_DT = Nz(Records!documentWisconsinFormRevisionDate, "")
                End If

                If rst!FORM_VERS_CD = "MUNI" Then
                    Set fld = Records.Fields("documentDocumentNumber")
                    If Len(fld & "") > 0 Then
                        rst!NON_UTC_NB = Nz(Records!documentDocumentNumber, "")
                    Else
                        rst!NON_UTC_NB = vbNull
                    End If
                End If
             
                'Set fld = Records.Fields("DNR_CITATION_NB")
                'If Len(fld & "") > 0 Then
                '    rst!NON_UTC_NB = Nz(Records!DNR_CITATION_NB, "")
                'End If

                Set fld = Records.Fields("violationDOTReportableFlag")
                If Len(fld & "") > 0 Then
                    If Records!violationDOTReportableFlag = "Y" Then
                        rst!DOT_MUNI_RPT_FL = -1
                    Else
                        rst!DOT_MUNI_RPT_FL = 0
                    End If
                End If


                Set fld = Records.Fields("violationLocalOrdinanceNumber")
                If Len(fld & "") > 0 Then
                    rst!LOR_NB = Nz(Records!violationLocalOrdinanceNumber, "")
                    Debug.Print ("LOR NB:" & rst!LOR_NB & "." & " len:" & Len(Nz(Records!violationLocalOrdinanceNumber, "")) & " len new:" & Len(rst!LOR_NB))
                End If

                Set fld = Records.Fields("violationStatuteNumber")
                If Len(fld & "") > 0 Then
                    rst!WI_STE_NB = Trim(Nz(Records!violationStatuteNumber, ""))
                End If

                Set fld = Records.Fields("violationStatuteSequenceNumber")
                If Len(fld & "") > 0 Then
                    rst!QWST_SEQ_NB = Trim(Nz(Records!violationStatuteSequenceNumber, ""))
                End If

                Set fld = Records.Fields("violationLocalOrdinanceDescription")
                If Len(fld & "") > 0 Then
                    rst!LOR_DESC = Trim(Left(Nz(Records!violationLocalOrdinanceDescription, ""), 45))
                    rst!Charge = Trim(Left(Nz(Records!violationLocalOrdinanceDescription, ""), 45))
                End If

                Set fld = Records.Fields("violationStatuteDescription")
                If Len(fld & "") > 0 Then
                    rst!Charge = Trim(Left(Nz(Records!violationStatuteDescription, ""), 45))
                End If

              rst.Update

            Records.MoveNext
        Loop

        Set fld = Nothing
    End If


    rst.Close
    Set rst = Nothing
    Set db = Nothing

End Sub

 

[spikepl]

What was your question again? You have a field in a record that you set to a fixed length of 25 and you are now surprised that when you call the Len function on that field it returns 25?

 

[lution]

Well, this is Access so field size sets the limit for how many characters the field will store. In this case the field is Text, Field Size = 25. I have two columns in the table with the exact same settings. Except, the LOR_NB field has extra characters tacked onto the end while WI_STE_NB doesn't. It makes comparing the result to existing data in the database difficult because the existing data doesn't have the extra characters on the end.

I modified the WI_STE_NB line to add a console line and here is what I get:
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.14(1). len:9 len new:9
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.37(1)(a)1. len:13 len new:13
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:341.04(1). len:9 len new:9
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.87. len:6 len new:6
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.33(1)(a). len:12 len new:12
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.46(1). len:9 len new:9
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:341.03(1). len:9 len new:9
LOR NB:7.01 . len:4 len new:25
WI_STE_NB:346.46(1). len:9 len new:9
LOR NB:7.05 . len:4 len new:25
WI_STE_NB:346.57(5). len:9 len new:9
LOR NB:8.01 . len:4 len new:25
WI_STE_NB:961.41(3g). len:10 len new:10
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(A) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:7.08(5)(a) . len:10 len new:25
LOR NB:8.08(5) . len:7 len new:25

You can see the "." is right after the last valid character from the source field while LOR_NB has extra something. In addition, the len new reflects the correct length.