SQL Syntax when converted to VB

[idono]

Hello all,
This may be really obvious to some, just not me. I'm trying to set the rowsource of a combobox to a new query when other comboboxes are updated and format the output. I tried this in the query builder and it works.

SELECT tblEmployeeInfo.EmployeeID, [Employee Last Name] & ", " & [Employee First Name] AS Expr1
FROM tblEmployeeInfo
ORDER BY [Employee Last Name] & "," & [Employee First Name];

The output returns "lastname, firstname"

I converted it into this in vb.

strSQL1 = "SELECT [tblEmployeeInfo].[EmployeeID], [Employee Last Name]" & ", " & "[Employee First Name] AS Expr1 FROM tblEmployeeInfo " & _
"WHERE [SupervisorID] = " & rst!SupervisorID & _
" ORDER BY [Employee Last Name]" & ", " & "[Employee First Name]"

The sql statment works but it does not format the output as a concatenated string "lastname, firstname".

any suggestions on getting the format right?

 

[Maquis]

I believe you have some extra quotes in there that are messing you up.

Delete the quote right after [Employee Last Name] and the quote before [Employee First Name] in the Select portion of your query. Maq B-)
<insert witty signature here>

 

[idono]

That yeilds an expected end of statement error.
You see anything else?

 

[Maquis]

Ack, you're right. I didn't read the sql close enough. Sorry 'bout that.

Try this:

Dim dQuote as string

dQuote = chr$(34)
strSQL1 = &quot;SELECT [tblEmployeeInfo].[EmployeeID], [Employee Last Name] & &quot; & dQuote & &quot;,&quot; & dQuote & &quot;[Employee First Name] AS Expr1 FROM tblEmployeeInfo WHERE [SupervisorID] = &quot; & rst!SupervisorID & &quot; ORDER BY [Employee Last Name]&quot; &quot;, &quot; & &quot;[Employee First Name]&quot;

(inserting line breaks where appropiate) Maq B-)
<insert witty signature here>

 

[KyleS]

Hi,
Maq was on the right trak, just traveling the wrong direction ;-)

anyways, since you're formating this as a string, you have to remember that the first set of quotes is to make your string a string, so in order to put &quot;, &quot; in a string you'll have to have &quot;&quot;&quot;&quot; & &quot;, &quot; & &quot;&quot;&quot;&quot; so that the string will read &quot;, &quot;.

But you also want the & to be in the string as wll so your string will have to look like this:

strSQL1 = &quot;SELECT [tblEmployeeInfo].[EmployeeID], [Employee Last Name] &&quot; & &quot;&quot;&quot;&quot; & &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;& [Employee First Name] AS Expr1 FROM tblEmployeeInfo &quot; & _
&quot;WHERE [SupervisorID] = &quot; & rst!SupervisorID & _
&quot; ORDER BY [Employee Last Name] &&quot; & &quot;&quot;&quot;&quot; & &quot;, &quot; & &quot;&quot;&quot;&quot; & &quot;&[Employee First Name]&quot;

I just pu this in a message box (with quotes aroung &quot;rst!supervisorID&quot; so I wouldn't get a &quot;Variable undefined&quot
and it came out exactly like what you're looking for.

Kyle :

 

[idono]

Hooooray! You are to be comended. I lost a lot of hair over that one.

Thanks,
Doug

 

[idono]

Here is what I did.

dQuote = Chr$(34)
strSQL1 = &quot;SELECT [tblEmployeeInfo].[EmployeeID], [Employee Last Name] & &quot; & dQuote & &quot;, &quot; & dQuote & &quot; & [Employee First Name] AS Expr1 FROM tblEmployeeInfo &quot; & _
&quot;WHERE [SupervisorID] = &quot; & rst!SupervisorID & _
&quot; ORDER BY [Employee Last Name] & &quot; & dQuote & &quot;, &quot; & dQuote & &quot; &[Employee First Name]&quot;

AccessIsFun was right I had to add a little, but it works. I just needed some help getting on the right track.

 

[Maquis]

Personally, any string with more than 4 quotes in it makes me dizzy. X-) s-) Maq B-)
<insert witty signature here>

 

[KyleS]

I have to agree Maq, but fortuneately I'm always dizzy so I don't really notice! (-: