VBA SQL syntax help needed

#1

I know this SQL code is correct because it was "generated" after
designing a query:
SELECT tblPeople.PersonID, [lastname] & ", " & [firstname] & " " &
[middlename] AS FullName
FROM tblPeople
ORDER BY [lastname] & ", " & [firstname] & " " & [middlename];

However, I do not know how to translate this for use in VBA for use as
a listbox's rowsource. The part throwing me off is where it combines
the lastname, firstname, and middlename fields AS fullname.

I want the listbox to just have one visible column (Fullname).

Thanks for any suggestions!
John

#2

John,
There is nothing to translate. It's fine just the way it is.

Set the Combo/List box RowSource Type to Table/Query.
Set your SQL as the Rowsource of the Combo/List box.
Set the Column Count property to 2, the bound column to 1,
and the Column Widths to 0";1" .
Only the fullname will show, but the ID number will be bound.
You hide a Combo or List box column by setting it's width to 0".

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

"John" <soundneedle (AT) hotmail (DOT) com> wrote

Quote:

I know this SQL code is correct because it was "generated" after
designing a query:
SELECT tblPeople.PersonID, [lastname] & ", " & [firstname] & " " &
[middlename] AS FullName
FROM tblPeople
ORDER BY [lastname] & ", " & [firstname] & " " & [middlename];

However, I do not know how to translate this for use in VBA for use as
a listbox's rowsource. The part throwing me off is where it combines
the lastname, firstname, and middlename fields AS fullname.

I want the listbox to just have one visible column (Fullname).

Thanks for any suggestions!
John

#3

The code works if placed in the control's rowsource field within
Access. It does not work when used in VBA. For example, why does this
not work:

Me.lstPeople.RowSource = "SELECT tblPeople.PersonID, [lastname] &" &
", " & " [firstname] AS FullName FROM tblPeople ORDER BY [lastname] &
[firstname];"

I know the problem is with...&" & ", " & "[firstname].... I'm not
getting the & and " right, but I can't figure out the right syntax.

"Fredg" <fgutkind (AT) att (DOT) net> wrote

Quote:

John,
There is nothing to translate. It's fine just the way it is.

Set the Combo/List box RowSource Type to Table/Query.
Set your SQL as the Rowsource of the Combo/List box.
Set the Column Count property to 2, the bound column to 1,
and the Column Widths to 0";1" .
Only the fullname will show, but the ID number will be bound.
You hide a Combo or List box column by setting it's width to 0".

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

"John" <soundneedle (AT) hotmail (DOT) com> wrote in message
news:90fab935.0308041657.3706a709 (AT) posting (DOT) google.com...
I know this SQL code is correct because it was "generated" after
designing a query:
SELECT tblPeople.PersonID, [lastname] & ", " & [firstname] & " " &
[middlename] AS FullName
FROM tblPeople
ORDER BY [lastname] & ", " & [firstname] & " " & [middlename];

However, I do not know how to translate this for use in VBA for use as
a listbox's rowsource. The part throwing me off is where it combines
the lastname, firstname, and middlename fields AS fullname.

I want the listbox to just have one visible column (Fullname).

Thanks for any suggestions!
John

#4

Nevermind, figured it out. Since VB interprets double quotes as a
single quote:
Me.lstPeople.RowSource = "SELECT tblPeople.PersonID, [lastname] & "",
"" & [firstname] AS FullName From tblPeople ORDER BY [lastname] & "",
"" & [firstname];"

"Fredg" <fgutkind (AT) att (DOT) net> wrote

Quote:

John,
There is nothing to translate. It's fine just the way it is.

Set the Combo/List box RowSource Type to Table/Query.
Set your SQL as the Rowsource of the Combo/List box.
Set the Column Count property to 2, the bound column to 1,
and the Column Widths to 0";1" .
Only the fullname will show, but the ID number will be bound.
You hide a Combo or List box column by setting it's width to 0".

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

"John" <soundneedle (AT) hotmail (DOT) com> wrote in message
news:90fab935.0308041657.3706a709 (AT) posting (DOT) google.com...
I know this SQL code is correct because it was "generated" after
designing a query:
SELECT tblPeople.PersonID, [lastname] & ", " & [firstname] & " " &
[middlename] AS FullName
FROM tblPeople
ORDER BY [lastname] & ", " & [firstname] & " " & [middlename];

However, I do not know how to translate this for use in VBA for use as
a listbox's rowsource. The part throwing me off is where it combines
the lastname, firstname, and middlename fields AS fullname.

I want the listbox to just have one visible column (Fullname).

Thanks for any suggestions!
John

#5

John,

Quote:

I need the code to return records as: "Doe, John". (The first code
outputs DoeJohn.)

You have extra &'s you don't need, you have to place a space between the ""
( " ") to get a space, and incorrect placement of quote marks.

Copy and Paste this directly into your code.

Me.lstPeople.RowSource = "SELECT tblPeople.PersonID, [lastname] & " " &
[firstname] AS FullName FROM tblPeople ORDER BY [lastname] & " " &
[firstname];"

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

"John" <soundneedle (AT) hotmail (DOT) com> wrote

Quote:

Maybe this is a better example...
This code returns records, note the "" between lastname and firstname:
Me.lstPeople.RowSource = "SELECT tblPeople.PersonID, [lastname] &" & "" &
"[firstname] AS FullName FROM tblPeople ORDER BY [lastname] & [firstname];"

This code does not return records, note the "," between lastname and
firstname:
Me.lstCompanies.RowSource = "SELECT tblPeople.PersonID, [lastname] &"
& "," & "[firstname] AS FullName FROM tblPeople ORDER BY [lastname] &
[firstname];"

I need the code to return records as: "Doe, John". (The first code
outputs DoeJohn.)

Thanks again for any help!
John

"Fredg" <fgutkind (AT) att (DOT) net> wrote

John,
There is nothing to translate. It's fine just the way it is.

Set the Combo/List box RowSource Type to Table/Query.
Set your SQL as the Rowsource of the Combo/List box.
Set the Column Count property to 2, the bound column to 1,
and the Column Widths to 0";1" .
Only the fullname will show, but the ID number will be bound.
You hide a Combo or List box column by setting it's width to 0".

--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.

"John" <soundneedle (AT) hotmail (DOT) com> wrote in message
news:90fab935.0308041657.3706a709 (AT) posting (DOT) google.com...
I know this SQL code is correct because it was "generated" after
designing a query:
SELECT tblPeople.PersonID, [lastname] & ", " & [firstname] & " " &
[middlename] AS FullName
FROM tblPeople
ORDER BY [lastname] & ", " & [firstname] & " " & [middlename];

However, I do not know how to translate this for use in VBA for use as
a listbox's rowsource. The part throwing me off is where it combines
the lastname, firstname, and middlename fields AS fullname.

I want the listbox to just have one visible column (Fullname).

Thanks for any suggestions!
John

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

VBA SQL syntax help needed

comp.databases.ms-access comp.databases.ms-access

VBA SQL syntax help needed - 08-04-2003 , 07:57 PM

Re: VBA SQL syntax help needed - 08-04-2003 , 10:19 PM

Re: VBA SQL syntax help needed - 08-05-2003 , 12:53 PM

Re: VBA SQL syntax help needed - 08-05-2003 , 01:43 PM

Re: VBA SQL syntax help needed - 08-05-2003 , 01:43 PM