Format a SSN 1

[MrsMope]

Hello,
I am inserting Social Security Numbers into a table (tblStagingTable), the field is set to text length of 9, with an input mask of social security number.
The tables containing the SSN are also formatted as listed above. My insert is done via VBA code:

Public Function ImportRecords()
'Specify the OLE DB provider and open the connection
Set cnnCobraInternet = CurrentProject.Connection
    strSQL = "SELECT DISTINCT SOC_SEC_NBR FROM tblCobraTransactions"
Set rstDistinctSSN = New ADODB.Recordset

rstDistinctSSN.Open strSQL, cnnCobraInternet, adOpenKeyset, adLockOptimistic
If rstDistinctSSN.EOF = True Then
    MsgBox "There are no records to import for specified range"
    intYesNo = 1
Else

rstDistinctSSN.MoveFirst
'Loop through all SSN's in in Internet Transaction table
Do While rstDistinctSSN.EOF = False
    'create second recordset for plan information
    Set rs = New ADODB.Recordset
    strSQL2 = "Select Travis_Code, ELIG, ASSGN_TRANs_NBR from [qrycontrol3] where SOC_SEC_NBR = " & "'" & rstDistinctSSN!SOC_SEC_NBR & "'"
    Debug.Print (strSQL2)

[red]IMMEDIATE WINDOW DISPLAYS:
Select Travis_Code, ELIG, ASSGN_TRANs_NBR from [qrycontrol3] where SOC_SEC_NBR = '05XXXXXXX'[/red]

    rs.Open strSQL2, cnnCobraInternet, adOpenKeyset, adLockOptimistic
    If rs.EOF = True Then
    Else
        rs.MoveFirst
    End If
    'loop through inner recordset, update the staging table with plan information
    Do While rs.EOF = False
        Dim intcount As Integer
        intcount = rs.RecordCount
       
        Select Case rs.AbsolutePosition
            Case 1 'Plan 1 insert only
                ins = "Insert into tblStagingTable(SSN,Control,Rel_Code,Plan_1,Note_Date,Elig_1,ASSGN_TRANs_NBR)values " & _
                "(" & Format(rstDistinctSSN!SOC_SEC_NBR, "000000000") & ", 3 ,'COVERAGE','" & rs!Travis_Code & "'," & _
                "" & Format(Date, "yyyy-mm-dd") & ", '" & rs!ELIG & "',  " & rs!ASSGN_TRANS_NBR & " )"
Debug.Print (ins)
                cnnCobraInternet.Execute ins

[red]IMMEDIATE WINDOW DISPLAYS:
Insert into tblStagingTable(SSN,Control,Rel_Code,Plan_1,Note_Date,Elig_1,ASSGN_TRANs_NBR)values (05XXXXXXX, 3 ,'COVERAGE','C01 C03 ',2005-11-07, 'A', 430978 )[/red]

This is what shows in the table:
[blue]
ASSGN_TRANS_NBR SSN
430978 5XX-XX-XXX
[/blue]

What is the deal?

 

[rubbernilly]

And this?

ins = "Insert into tblStagingTable(SSN,Control,Rel_Code,Plan_1,Note_Date,Elig_1,ASSGN_TRANs_NBR)values " & _
"('" & Format(rstDistinctSSN!SOC_SEC_NBR, "000000000") & "', 3 ,'COVERAGE','" & rs!Travis_Code & "'," & _
"" & Format(Date, "yyyy-mm-dd") & ", '" & rs!ELIG & "', " & rs!ASSGN_TRANS_NBR & " )"
Debug.Print (ins)

 

[MrsMope]

Rubbernilly,
the red beneath the second code window is the immediate window
[red]IMMEDIATE WINDOW DISPLAYS:
Insert into tblStagingTable(SSN,Control,Rel_Code,Plan_1,Note_Date,Elig_1,ASSGN_TRANs_NBR)values (05XXXXXXX, 3 ,'COVERAGE','C01 C03 ',2005-11-07, 'A', 430978 )[/red]

Once that code runs, the table the record was inserted into shows:
[blue]
ASSGN_TRANS_NBR SSN
430978 5XX-XX-XXX
[/blue]


Thanks,

 

[rubbernilly]

If I understand you correctly, you want to know why a SSN that begins with '0' loses that zero with the SQL commits the record to the table?

If so, the code I provided you should help with that.

NOTE: the code I provided to you is different than what you had. Your first SQL statement treats SSN as a text value. Your second does not. Mine does.

 

[MrsMope]

Rubbernilly,
Thanks so much, I misunderstood your post. I thought you were asking me what that statement produced in the immediate window. Using the code you provided, the leading zero now inserts correctly.