joining two strings before query

[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

 

[Gina Whipp]

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

 

[John W. Vinson]

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

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.

 

[Associates]

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

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

 

[Duane Hookom]

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

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

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.

 

[John Spencer]

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

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.