Converting SQL to VBA for report

  • Thread starter Thread starter Saabster
  • Start date Start date
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
 
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.
 
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.
 
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, 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...
 
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.
 
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
 
Back
Top