Extra spacing being put in Custom MsgBox

[gold007eye]

I am in the process of converting my DB into SQL. What I can't understand is why since doing so is my custom error message putting a bunch of unwanted spaces in the message? (See attached image)

Here is the code I am using. This was working fine until the tables were moved to a SQL backend Is there something I need to do differently?

Select Case MsgBox("Are you sure you want to remove the associate " & [Employee Name] & " from the list?", vbYesNo + vbQuestion, "Remove the Associate " & [Employee Name] & "?")

Case vbYes: 'Delete the record
    DoCmd.SetWarnings False
    DoCmd.RunCommand acCmdDeleteRecord
    DoCmd.SetWarnings True
    MsgBox "This associate has been removed from the list" & vbCrLf & vbCrLf & "Click OK to continue...", vbCritical, "Associate Removed!"
Case vbNo: 'Do NOT delte the record
    DoCmd.CancelEvent
    MsgBox "Associate Removal Cancelled!", vbInformation, "Associate Removal Cancelled!"
Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
'Code End

 

[chergh]

Try using the trim function on the employee name field, I seem to recall SQL server pads cells so if you have a 50 char text field defined it will just add blank spaces till it reaches its 50 character field length.

trim([Employee Name])

 

[boblarson]

I seem to recall SQL server pads cells so if you have a 50 char text field defined it will just add blank spaces till it reaches its 50 character field length.

Yes, unless you make the field a varchar type, which is a good thing to do.

 

[georgedwilkinson]

Also, make sure you're using varchar or nvarchar data types for all text fields.

 

[gold007eye]

Thanks for the quick response all That was the issue. Now I had our SQL Admin tell me to use Char (if you are using a specific field size) VarChar (for non-specific field size) and never use nVarChar due to it using twice the space to store data.

 

[boblarson]

 

[georgedwilkinson]

Thanks for the quick response all That was the issue. Now I had our SQL Admin tell me to use Char (if you are using a specific field size) VarChar (for non-specific field size) and never use nVarChar due to it using twice the space to store data.

Huh?

Always use varchar (for domestic) or nvarchar (for international). Char causes you to spend all your time on forums trying to figure out why your stuff doesn't work. You can specify the maximum length of both varchar and nvarchar, so no problem there.

Is your company under some budget crisis where you can't afford additional hard-drive space? If not, I recommend nvarchar since you never know if your system will go international or not.

 

[gold007eye]

Thanks for the reply. I think I will change the stuff back to varchar. The databases I design or strictly domestic so I shouldn't have a need for the nVarChar.

I will try out these changes tomorrow.