SQL Woes

[Guest]

I am writing a source string for a combo box recordsource. In an attempt to
improve the speed I am referencing the table rather than a query. In fact,
now that I am getting better with SQL I am trying to get rid of most of the
querys. Anyway, in the recordsource I want the first name and last name
concatenated as Full. This concatenation occures in the query, but obviously,
not the table. I am also adding in the user entry as a string with an
asterisk. I think there is something wrong with the quotation marks, too many
or too few, who knows. The error message is that it is looking for the end of
the statement. The code:
Source = "SELECT [tbl Raw Data].LogID, [tbl Raw Data].Account, [tbl Raw
Data].Short, [tbl Source].SourceShort, [tbl Raw Data].Code, [tbl
Employee].[FirstName] & " " & [tbl Employee].[LastName] AS [Full] FROM ([tbl
Raw Data] LEFT JOIN [tbl Source] ON [tbl Raw Data].SourceID = [tbl
Source].SourceID) LEFT JOIN [tbl Employee] ON [tbl Raw Data].HRepEID = [tbl
Employee].EID WHERE ((([tbl Raw Data].Account) Like """ & strAcct & "*""))
ORDER BY [tbl Raw Data].Account, [tbl Source].SourceShort;"
Any help would be appreciated, I am getting frustrated.

 

[Douglas J. Steele]

Anytime you want quotes inside a quoted string, you need to double them up:

....[tbl Employee].[FirstName] & "" "" & [tbl Employee].[LastName] AS
[Full]...

Alternatively, you could use single quotes:

....[tbl Employee].[FirstName] & ' ' & [tbl Employee].[LastName] AS [Full]...

 

[Guest]

Douglas,
Both the double double and the single single worked. I guess I was
influenced by the string at the end that needs three doubles to work. Thanks
very much.
Michael
--
Stamford, CT

Anytime you want quotes inside a quoted string, you need to double them up:

....[tbl Employee].[FirstName] & "" "" & [tbl Employee].[LastName] AS
[Full]...

Alternatively, you could use single quotes:

....[tbl Employee].[FirstName] & ' ' & [tbl Employee].[LastName] AS [Full]...

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am writing a source string for a combo box recordsource. In an attempt to
improve the speed I am referencing the table rather than a query. In fact,
now that I am getting better with SQL I am trying to get rid of most of
the
querys. Anyway, in the recordsource I want the first name and last name
concatenated as Full. This concatenation occures in the query, but
obviously,
not the table. I am also adding in the user entry as a string with an
asterisk. I think there is something wrong with the quotation marks, too
many
or too few, who knows. The error message is that it is looking for the end
of
the statement. The code:
Source = "SELECT [tbl Raw Data].LogID, [tbl Raw Data].Account, [tbl Raw
Data].Short, [tbl Source].SourceShort, [tbl Raw Data].Code, [tbl
Employee].[FirstName] & " " & [tbl Employee].[LastName] AS [Full] FROM
([tbl
Raw Data] LEFT JOIN [tbl Source] ON [tbl Raw Data].SourceID = [tbl
Source].SourceID) LEFT JOIN [tbl Employee] ON [tbl Raw Data].HRepEID =
[tbl
Employee].EID WHERE ((([tbl Raw Data].Account) Like """ & strAcct & "*""))
ORDER BY [tbl Raw Data].Account, [tbl Source].SourceShort;"
Any help would be appreciated, I am getting frustrated.