missing zeros at beginning of zip code

[madEG]

Hello,

I have a accdb pointed at Sql Server 2012 via ODBC pushing text from a text box back to a Sql server's column that is a nvarchar(10) to store zip codes.

Edit: I confirmed that the accdb's linked table has a datatype of text for the zip code column.

Oddly, the Access form's textbox is sending back the text, but stripping out the preceeding zero in the zip code - almost like when excel (shudder) strips the preceeding zeros away from SSNs and Zipcodes - treating them like a number - and not text.

How do I stop this?

The form object is a text box, and the sql server backend doesn't mind the preceeding zero. When I manually set the value to = '01111' using sql server management studio, the value is accepted - and then later viewable (with the zero) when using the accdb's form...

This leads me to believe the form is stripping away the zero when inserting the record. The add records button uses the following to insert the records:

Private Sub btnAddDioOffice_Click()
  Dim strSQL As String
    
    If ListBoxDios.Value <> "" And txtDioOffName.Value <> "" Then
        strSQL = "INSERT INTO dbo_tblDioOffice (DioCode, DioOfficeName, DioOfficeAddress1, DioOfficeAddress2, DioOfficeAddress3, DioOfficeCity, DioOfficeState, DioOfficeZip)" & _
        "VALUES ('" & ListBoxDios.Value & "" _
        & "','" & Replace(Format(txtDioOffName), "'", "''") _
        & "','" & Replace(Format(txtDioOffAddr1), "'", "''") _
        & "','" & Replace(Format(txtDioOffAddr2), "'", "''") _
        & "','" & Replace(Format(txtDioOffAddr3), "'", "''") _
        & "','" & Replace(Format(txtDioOffCity), "'", "''") _
        & "','" & Replace(Format(txtDioOffState), "'", "''") _
        & "','" & Replace(Format(txtDioOffZip), "'", "''") _
        & "');"
        
        DoCmd.SetWarnings False
        'MsgBox ("strSQL: " & strSQL)
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
    
        txtDioOffName = ""
        txtDioOffAddr1 = ""
        txtDioOffAddr2 = ""
        txtDioOffAddr3 = ""
        txtDioOffCity = ""
        txtDioOffState = ""
        txtDioOffZip = ""
    
        ListBoxDioOffices.Requery  ' requery Persons List Box
    Else
        MsgBox ("Select a Diocese and enter (minimally) and Office Name before adding the new record.")
    End If
        
        
End Sub

I've tried removing the format() and the trim() to no avail... Not sure what else to try next.

Thoughts?

Thanks!
-Matt G.

 

[michaeljryan78]

you could test the length of DioOfficeZip and append the zero...

if len(DioOfficeZip)<5 then
  DioOfficeZip = "0" & DioOfficeZip"
end if

 

[madEG]

...you know what, I tested it again removing the format() and it now works properly. (I must have messed up my testing that.)

Oddly, I confirmed I have another app where the format() is used to thwart adding null in the insert VBA (against a sql server varchar(10) in this case) and it doesn't seem to have this problem. Weird.

I thought my using format on strings would make them blank rather than null... which is why I tended to do this...

Ok, so drop the format().

Thanks! And thanks Michael! I was going there next if I couldn't figure it out