Concatenation and quotes

  • Thread starter Thread starter Richard
  • Start date Start date
R

Richard

Hi

You want to try this

strSQL = "SELECT DISTINCTROW ([FirstName]+" ") & ([Middle]+" ") & [LastName]
& (", "+[Suffix]) AS Name, "
 
strSQL = "SELECT DISTINCTROW [FirstName] & ' ' & [Middle] & ' ' & [LastName] & ' (, ' &
[Suffix] & ')' AS Name, "

Note the space between the single quotes. Are you wanting the parenthesis around the
[Suffix]? You also have the comma after the (.

Also, I don't recommend using Name as the field name. Name is a reserved word. Perhaps try

As WholeName
 
In VBE, I'm writing a SQL string that combines firstm middle, last and
suffix names. I can't get the quotes right.

I can do this easily is ASP code, but having trouble in access. Any ideas?


strSQL = "SELECT DISTINCTROW [FirstName]+" " & [Middle]+" " & [LastName]
& (", "+[Suffix]) AS Name, "
 
it's still giving me an error. would you copy and paste into VBE and see?


Richard said:
Hi

You want to try this

strSQL = "SELECT DISTINCTROW ([FirstName]+" ") & ([Middle]+" ") & [LastName]
& (", "+[Suffix]) AS Name, "


Scott said:
In VBE, I'm writing a SQL string that combines firstm middle, last and
suffix names. I can't get the quotes right.

I can do this easily is ASP code, but having trouble in access. Any ideas?


strSQL = "SELECT DISTINCTROW [FirstName]+" " & [Middle]+" " & [LastName]
& (", "+[Suffix]) AS Name, "
Click to expand...
Click to expand...
 
i made it work by just using double quotes inside sql statement.

thanks for the input.

Wayne Morgan said:
strSQL = "SELECT DISTINCTROW [FirstName] & ' ' & [Middle] & ' ' & [LastName] & ' (, ' &
[Suffix] & ')' AS Name, "

Note the space between the single quotes. Are you wanting the parenthesis around the
[Suffix]? You also have the comma after the (.

Also, I don't recommend using Name as the field name. Name is a reserved word. Perhaps try

As WholeName


--
Wayne Morgan
Microsoft Access MVP


Scott said:
In VBE, I'm writing a SQL string that combines firstm middle, last and
suffix names. I can't get the quotes right.

I can do this easily is ASP code, but having trouble in access. Any ideas?


strSQL = "SELECT DISTINCTROW [FirstName]+" " & [Middle]+" " & [LastName]
& (", "+[Suffix]) AS Name, "
Click to expand...
Click to expand...
 
You might want to try doubling the quotes inside the string. This is will
handle names Like O'Casey and O'Hara without generating errors.

strSQL = "SELECT DISTINCTROW [FirstName] & "" "" & [Middle] & "" "" &
[LastName] & "" (, "" & [Suffix] & "")"" AS Name, "


Wayne said:
strSQL = "SELECT DISTINCTROW [FirstName] & ' ' & [Middle] & ' ' & [LastName] & ' (, ' &
[Suffix] & ')' AS Name, "

Note the space between the single quotes. Are you wanting the parenthesis around the
[Suffix]? You also have the comma after the (.

Also, I don't recommend using Name as the field name. Name is a reserved word. Perhaps try

As WholeName

--
Wayne Morgan
Microsoft Access MVP

Scott said:
In VBE, I'm writing a SQL string that combines firstm middle, last and
suffix names. I can't get the quotes right.

I can do this easily is ASP code, but having trouble in access. Any ideas?


strSQL = "SELECT DISTINCTROW [FirstName]+" " & [Middle]+" " & [LastName]
& (", "+[Suffix]) AS Name, "
Click to expand...
Click to expand...
 
Back
Top