Combo Box from SQL Server

  • Thread starter Thread starter Doc
  • Start date Start date
D

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!
 
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.
 
Works like a charm!

Thanks!

raskew via AccessMonster.com said:
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!
Click to expand...
Click to expand...
 
Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Sylvain Lafontaine said:
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)


Doc said:
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!
Click to expand...
Click to expand...
 
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)


Doc said:
Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Sylvain Lafontaine said:
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)


Doc said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the information!

Sylvain Lafontaine said:
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)


Doc said:
Is the preferred data type is sql nvarchar?

I'm still fairly new to the SQL world

Sylvain Lafontaine said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top