Double Quotes and SQL

[Scott]

I'm trying to change the RowSource of a combo box on the OnLoad event. I
need to concatenate the "userLast" and "userFirst" fields into "userName" as
seen in my code below.

How do I deal with the needed quotes that combine the "userLast" and
"userFirst" fields? Can someone correct my concatenation double quotes
below?

CODE ****************

sSQL = "SELECT userID, [userLast] & "", "" & [userFirst] AS userName" &
_
" FROM t_users"

Me.cboFind.RowSource = sSQL

 

[Ken Snell \(MVP\)]

I usually use ' character within a string like this:

sSQL = "SELECT userID, [userLast] & ', ' & [userFirst] AS userName" & _
" FROM t_users"

Otherwise, doubling up the " character should work, which is how you posted
the string. What you posted should work as well.

Is what you posted not working?

 

[Dave]

Hi Scott.
I'm assuming [userLast] and [UserFirst] are two controls on your form.

Try this out:
dim sUser as string
dim sSQL as string

sUser = me![userLast] & me![userFirst]
sSQL = "SELECT userID, " & sUser & " AS userName FROM t_users;"

Hope that helped.
Cheers
Dave

 

[Dave]

Scott,
Where are the userFirst and userLast values coming from? An open form? You
may need to reference the form explicitly first. Try this: (watch out for
word wrapping)

Dim sUser as string
Dim sSQL as string

sUser = me![NameOfYourForm]![userLast] & ", " &
me![NameOfYourForm]![userFirst]

sSQL = "SELECT userID, " & sUser & " AS userName FROM t_users;"

me.recordsource = sSQL

Hope that helped.
Dave

 

[Scott]

No, userFirst and userLast are fields in my table. I've got to be able to
include both of them within the sql statement to get the full name variable
called "userName".

Hi Scott.
I'm assuming [userLast] and [UserFirst] are two controls on your form.

Try this out:
dim sUser as string
dim sSQL as string

sUser = me![userLast] & me![userFirst]
sSQL = "SELECT userID, " & sUser & " AS userName FROM t_users;"

Hope that helped.
Cheers
Dave

I'm trying to change the RowSource of a combo box on the OnLoad event. I
need to concatenate the "userLast" and "userFirst" fields into "userName"
as
seen in my code below.

How do I deal with the needed quotes that combine the "userLast" and
"userFirst" fields? Can someone correct my concatenation double quotes
below?

CODE ****************

sSQL = "SELECT userID, [userLast] & "", "" & [userFirst] AS userName"
&
_
" FROM t_users"

Me.cboFind.RowSource = sSQL

 

[Scott]

i got it using single quotes, thanks.

Scott,
Where are the userFirst and userLast values coming from? An open form? You
may need to reference the form explicitly first. Try this: (watch out for
word wrapping)

Dim sUser as string
Dim sSQL as string

sUser = me![NameOfYourForm]![userLast] & ", " &
me![NameOfYourForm]![userFirst]

sSQL = "SELECT userID, " & sUser & " AS userName FROM t_users;"

me.recordsource = sSQL

Hope that helped.
Dave

I'm trying to change the RowSource of a combo box on the OnLoad event. I
need to concatenate the "userLast" and "userFirst" fields into "userName"
as
seen in my code below.

How do I deal with the needed quotes that combine the "userLast" and
"userFirst" fields? Can someone correct my concatenation double quotes
below?

CODE ****************

sSQL = "SELECT userID, [userLast] & "", "" & [userFirst] AS userName"
&
_
" FROM t_users"

Me.cboFind.RowSource = sSQL