Extra spacing being put in Custom MsgBox

gold007eye

Registered User.
Local time
Today, 09:26
Joined
May 11, 2005
Messages
260
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?

Code:
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
 

Attachments

  • MsgBox_Issue.jpg
    MsgBox_Issue.jpg
    11.8 KB · Views: 101
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.

Code:
trim([Employee Name])
 
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.
 
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.
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom