Combo Box from SQL Server

[Doc]

Attempting to populate a combo box from an SQL server with multiple fields.
Returns fields with all empty spaces.

Example:

SELECT dbo_tblPeople.IDPeople, [LName] & ", " & FName & " " & MName as Name
FROM dbo_tblPeople

Sample Data Returned:

"Doe , John A."

Would like:

"Doe, John A."

I've used Allen Brownes suggestion using the 'Replace()' command, which
works, but I'm hoping for something as simple as a modification to the 'Row
Source'.

Thanks!

 

[Sylvain Lafontaine]

Probably because your string fields like LName are of type char() and/or
nchar() instead of varchar() and nvarchar(). It's also possible that you
are using fields of type varchar() and nvarchar() but the method used to
store these values has inserted these trailing blanks along.

 

[Doc]

Works like a charm!

Thanks!

Hi -
Try enclosing each field name with the RTrim() function, e.g.:

SELECT rtrim(dbo_tblPeople.IDPeople), rtrim([LName]) & ", " & rtrim(FName) &
" " & rtrim(MName) as Name
FROM dbo_tblPeople

HTH - Bob

Attempting to populate a combo box from an SQL server with multiple fields.
Returns fields with all empty spaces.

Example:

SELECT dbo_tblPeople.IDPeople, [LName] & ", " & FName & " " & MName as Name
FROM dbo_tblPeople

Sample Data Returned:

"Doe , John A."

Would like:

"Doe, John A."

I've used Allen Brownes suggestion using the 'Replace()' command, which
works, but I'm hoping for something as simple as a modification to the 'Row
Source'.

Thanks!

 

[Doc]

Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Probably because your string fields like LName are of type char() and/or
nchar() instead of varchar() and nvarchar(). It's also possible that you
are using fields of type varchar() and nvarchar() but the method used to
store these values has inserted these trailing blanks along.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Attempting to populate a combo box from an SQL server with multiple
fields.
Returns fields with all empty spaces.

Example:

SELECT dbo_tblPeople.IDPeople, [LName] & ", " & FName & " " & MName as
Name
FROM dbo_tblPeople

Sample Data Returned:

"Doe , John A."

Would like:

"Doe, John A."

I've used Allen Brownes suggestion using the 'Replace()' command, which
works, but I'm hoping for something as simple as a modification to the
'Row
Source'.

Thanks!

 

[Sylvain Lafontaine]

If you want to use Unicode (2 bytes per character) in order to easily store
foreign characters, then the preferred data type would be nvarchar or ntext
for very long text (longer than 4000 characters); if you don't want Unicode
then it would be varchar or text for very long text (greater than 8000
characters).

char and nchar should only be used when the length of the strings are
constant; for exemple for storing a serial number or an alphanumeric code.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Probably because your string fields like LName are of type char() and/or
nchar() instead of varchar() and nvarchar(). It's also possible that you
are using fields of type varchar() and nvarchar() but the method used to
store these values has inserted these trailing blanks along.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Attempting to populate a combo box from an SQL server with multiple
fields.
Returns fields with all empty spaces.

Example:

SELECT dbo_tblPeople.IDPeople, [LName] & ", " & FName & " " & MName as
Name
FROM dbo_tblPeople

Sample Data Returned:

"Doe , John A."

Would like:

"Doe, John A."

I've used Allen Brownes suggestion using the 'Replace()' command, which
works, but I'm hoping for something as simple as a modification to the
'Row
Source'.

Thanks!

 

[Doc]

Thanks for the information!

If you want to use Unicode (2 bytes per character) in order to easily store
foreign characters, then the preferred data type would be nvarchar or ntext
for very long text (longer than 4000 characters); if you don't want Unicode
then it would be varchar or text for very long text (greater than 8000
characters).

char and nchar should only be used when the length of the strings are
constant; for exemple for storing a serial number or an alphanumeric code.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Probably because your string fields like LName are of type char() and/or
nchar() instead of varchar() and nvarchar(). It's also possible that you
are using fields of type varchar() and nvarchar() but the method used to
store these values has inserted these trailing blanks along.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Attempting to populate a combo box from an SQL server with multiple
fields.
Returns fields with all empty spaces.

Example:

SELECT dbo_tblPeople.IDPeople, [LName] & ", " & FName & " " & MName as
Name
FROM dbo_tblPeople

Sample Data Returned:

"Doe , John A."

Would like:

"Doe, John A."

I've used Allen Brownes suggestion using the 'Replace()' command, which
works, but I'm hoping for something as simple as a modification to the
'Row
Source'.

Thanks!