Building SQL-string with vba

[Jan T.]

Here is what I want to put into a variabel; strSQL.
However, I am experiencing some difficulties whith the spaces. What do I do
wrong?

I copied this from my Query-builder in Access 2000.
----------------------------------------------------------------
SELECT [Firstname] & " " & [Lastname] AS Employee
FROM Employees
WHERE ((([Firstname] & " " & [Lastname]) Like [Forms]![myForm].[txtName] &
"*"));


I tried different combinations of ", """, """" """", "'" "'" and so
on...

Like...:
strSQL = "SELECT [Firstname] & "'" "'" & [Lastname] AS Employee"
strSQL = strSQL & " FROM Employees"
strSQL = strSQL & " WHERE ((([Firstname] & "'" "'" & [Lastname]) Like
[Forms]![myForm].[txtName] & '*'));"

But, as you might see, this fails. Any suggestions?

Thank you in advance!

Regards
Jan

 

[Bob Phillips]

How about

"SELECT [Firstname] &' ' & [Lastname] AS Employee " & _
"FROM Employees " & _
"WHERE ((([Firstname] & ' ' & [Lastname]) Like [Forms]![myForm].[txtName] &
"*"));"



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jan T.]

Ah, of course! I just couldn't remember.
Thanks a lot!
'*'

Jan

How about

"SELECT [Firstname] &' ' & [Lastname] AS Employee " & _
"FROM Employees " & _
"WHERE ((([Firstname] & ' ' & [Lastname]) Like [Forms]![myForm].[txtName]
& "*"));"



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Here is what I want to put into a variabel; strSQL.
However, I am experiencing some difficulties whith the spaces. What do I
do wrong?

I copied this from my Query-builder in Access 2000.
----------------------------------------------------------------
SELECT [Firstname] & " " & [Lastname] AS Employee
FROM Employees
WHERE ((([Firstname] & " " & [Lastname]) Like [Forms]![myForm].[txtName]
& "*"));


I tried different combinations of ", """, """" """", "'" "'" and so
on...

Like...:
strSQL = "SELECT [Firstname] & "'" "'" & [Lastname] AS Employee"
strSQL = strSQL & " FROM Employees"
strSQL = strSQL & " WHERE ((([Firstname] & "'" "'" & [Lastname]) Like
[Forms]![myForm].[txtName] & '*'));"

But, as you might see, this fails. Any suggestions?

Thank you in advance!

Regards
Jan