SQL Woes

M

Michael Conroy

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.
 
D

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]...
 
M

Michael Conroy

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


Douglas J. Steele said:
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!)


Michael Conroy said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top