Converting SQL to VBA for report

S

Saabster

Hi all,

I'm trying to convert an SQL statement to VBA to use as a recordsource
for a report. I've got most of it done, but the following section is
giving me fits.... How the hell do I do this?

SELECT tblResourcesFirstName & " " & TblResourcesLastName AS FullName,

I can't seem to be able to combine the first and last name as an
expression.

Thanks

Craig
 
D

Duane Hookom

If you provided more code or whatever, someone might be able to hellp. It
may be you need to replace those double-quotes with single-quotes.
 
A

Allen Browne

The issue is probably the double-quote character you need inside the string?
If so, you need to double-them up:
"SELECT tblResourcesFirstName & "" "" & TblResourcesLastName AS
FullName, "

A poor idea would be to use a single quote as the delimiter instead:
"SELECT tblResourcesFirstName & ' ' & TblResourcesLastName AS FullName,"
That's going to fail for the O'Briens etc.
 
A

Aubrey

Two things; Use + (Plus Sign) for Concatenation, Use Single Quote
(apostrophe) for Literal. This is a Cut-n-Paste for one of my Projects:

SELECT TOP 100 PERCENT dbo.Patients.[Patient Name Last] + N', ' +
dbo.Patients.[Patient Name First] AS PtName, . . .
 
A

Allen Browne

Aubrey, I could not recommend either of those techniques.

As already pointed out, the single-quote fails as soon as you have a name
containing an apostrophe - surprisingly common.

Further, the & is a preferable concatenation operator, since the + can
function as an addition operator or a concatenation operator. Text1 contains
1 and Text2 contains 2, then
[Text1] + [Text2]
is ambiguous. It might return 3, and it might return 12, and I could craft
examples of both (by setting the Format of unbound text boxes.)

There are some cases where + is useful for concatenation (because it handles
Null differently than & does), but in general,
ampersand is preferable for concatenation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Aubrey said:
Two things; Use + (Plus Sign) for Concatenation, Use Single Quote
(apostrophe) for Literal. This is a Cut-n-Paste for one of my Projects:

SELECT TOP 100 PERCENT dbo.Patients.[Patient Name Last] + N', ' +
dbo.Patients.[Patient Name First] AS PtName, . . .
--
Aubrey Kelley


Allen Browne said:
The issue is probably the double-quote character you need inside the
string?
If so, you need to double-them up:
"SELECT tblResourcesFirstName & "" "" & TblResourcesLastName AS
FullName, "

A poor idea would be to use a single quote as the delimiter instead:
"SELECT tblResourcesFirstName & ' ' & TblResourcesLastName AS
FullName,"
That's going to fail for the O'Briens etc.
Click to expand...
Click to expand...
 
S

Saabster

Allen,

I'm trying to concatonate 2 fields as one, with a space between it.
so Firstname is Craig Last name is Bobchin I want to create full name
as Craig Bobchin.

All the fiddling around I've done has not been successful.

I tried your suggestion and it returned the following as the SQL:
SELECT tblResources.FirstName " & " tblResources.LastName AS FullName,
which gave me a syntax error in the query when I tried to run it.
 
A

Allen Browne

If you are typing into the query design window, try:

SELECT tblResources.FirstName & " " & tblResources.LastName AS FullName FROM
tblResources;

That means:
Give me the FirstName field, and a space, and the LastName field, and call
it FullName
 
Top