joining two strings before query

  • Thread starter Thread starter Associates
  • Start date Start date
A

Associates

Hi,

I have a query in regards to SQL query. I was trying to write a query to
concatenate two string together as below

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] &
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I got an error (End of Statement) coming from the part where i would like to
combine firstname and lastname with a space in between them. I think i got
the double quotation wrong there. I wonder if anyone might be able to point
me to the right direction.

Thank you in advance
 
Associates,

If this is on two lines then...

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & " " & " & _
"[Last_Name] AS Expr1, [Job Schedule].Rate, [Job
Schedule].JSchedule_ID;"

If this is on one line then (watch out for word wrap)...

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & " "
&[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID;"

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm
 
Hi,

I have a query in regards to SQL query. I was trying to write a query to
concatenate two string together as below

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] &
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I got an error (End of Statement) coming from the part where i would like to
combine firstname and lastname with a space in between them. I think i got
the double quotation wrong there. I wonder if anyone might be able to point
me to the right direction.

Thank you in advance
Click to expand...

If this is literally copied from your VBA code without your newsreader
word-wrapping it, your first line (the one ending in & ) is left incomplete,
and the second line is meaningless.

You can use a "Line Continuation" by ending the first line with a blank space
followed by an underscore: e.g. the VBA expression

X = A _
+ B

is exactly equivalent to

X = A + B

In your case, it may need to be

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & _
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

although it's often safer to keep string literals unsplit:

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID," _
& "[First_Name] & [Last_Name] AS Expr1, " _
& "[Job Schedule].Rate, [Job Schedule].JSchedule_ID"

using the blank-underscore continuation character to let you put one statement
on three lines, each containing a portion of the quoted string literal.
 
Thanks for your reply, Gina. It is all in one line.

It still does not work yet. The problem lies on where there is double
quotation between First_Name and Last_Name.

It does not like [First_Name] & " " & [Last_Name] AS Expr1 because i already
have double quotation at the start as follow.

strSQL = "SELECT DISTINCTROW [Job Schedule].Schedule_ID,[First_Name] & " " &
[Last_Name] AS Expr1, ...


The aim is to have a space in between First Name and Last Name. For example,
Mike Smith rather than MikeSmith.

Hope i explained it well.

Thank you in advance

Gina Whipp said:
Associates,

If this is on two lines then...

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & " " & " & _
"[Last_Name] AS Expr1, [Job Schedule].Rate, [Job
Schedule].JSchedule_ID;"

If this is on one line then (watch out for word wrap)...

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & " "
&[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID;"

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Associates said:
Hi,

I have a query in regards to SQL query. I was trying to write a query to
concatenate two string together as below

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] &
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I got an error (End of Statement) coming from the part where i would like
to
combine firstname and lastname with a space in between them. I think i got
the double quotation wrong there. I wonder if anyone might be able to
point
me to the right direction.

Thank you in advance
Click to expand...
Click to expand...
 
If you want a blank/space between the first and last names, try:
strSQL = "SELECT DISTINCTROW [Job].Schedule_ID," _
& "[First_Name] & ' ' & [Last_Name] AS FullName, " _
& "[Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I'm not sure what happened to your FROM clause but I assume you know where
it is.
--
Duane Hookom
Microsoft Access MVP


John W. Vinson said:
Hi,

I have a query in regards to SQL query. I was trying to write a query to
concatenate two string together as below

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] &
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I got an error (End of Statement) coming from the part where i would like to
combine firstname and lastname with a space in between them. I think i got
the double quotation wrong there. I wonder if anyone might be able to point
me to the right direction.

Thank you in advance
Click to expand...

If this is literally copied from your VBA code without your newsreader
word-wrapping it, your first line (the one ending in & ) is left incomplete,
and the second line is meaningless.

You can use a "Line Continuation" by ending the first line with a blank space
followed by an underscore: e.g. the VBA expression

X = A _
+ B

is exactly equivalent to

X = A + B

In your case, it may need to be

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & _
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

although it's often safer to keep string literals unsplit:

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID," _
& "[First_Name] & [Last_Name] AS Expr1, " _
& "[Job Schedule].Rate, [Job Schedule].JSchedule_ID"

using the blank-underscore continuation character to let you put one statement
on three lines, each containing a portion of the quoted string literal.
Click to expand...
 
Try the following. Not the two quotes in a row. When the string is generated
the two quotes will become one

So
strSQL = "SELECT DISTINCTROW [Job].Schedule_ID," _
& "[First_Name] & "" "" & [Last_Name] AS Expr1, " _
& "[Job Schedule].Rate, [Job Schedule].JSchedule_ID"

will create the following string

SELECT DISTINCTROW [Job].Schedule_ID,[First_Name] & " " & [Last_Name] AS
Expr1,[Job Schedule].Rate, [Job Schedule].JSchedule_ID

Note that this is still not a complete SQL statement.
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Hi,

I have a query in regards to SQL query. I was trying to write a query to
concatenate two string together as below

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] &
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

I got an error (End of Statement) coming from the part where i would like to
combine firstname and lastname with a space in between them. I think i got
the double quotation wrong there. I wonder if anyone might be able to point
me to the right direction.

Thank you in advance
Click to expand...

If this is literally copied from your VBA code without your newsreader
word-wrapping it, your first line (the one ending in & ) is left incomplete,
and the second line is meaningless.

You can use a "Line Continuation" by ending the first line with a blank space
followed by an underscore: e.g. the VBA expression

X = A _
+ B

is exactly equivalent to

X = A + B

In your case, it may need to be

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID, [First_Name] & _
[Last_Name] AS Expr1, [Job Schedule].Rate, [Job Schedule].JSchedule_ID"

although it's often safer to keep string literals unsplit:

strSQL = "SELECT DISTINCTROW [Job].Schedule_ID," _
& "[First_Name] & [Last_Name] AS Expr1, " _
& "[Job Schedule].Rate, [Job Schedule].JSchedule_ID"

using the blank-underscore continuation character to let you put one statement
on three lines, each containing a portion of the quoted string literal.
Click to expand...
 
Back
Top