need help with strSQL

[Russ via AccessMonster.com]

How can I write this as a string SQL

SELECT [FirstName] & " " & [LastName] AS FullName
FROM tblContacts INNER JOIN tblReservations ON tblContacts.ContactID =
tblReservations.ResName
WHERE ((([FirstName] & " " & [LastName]) Is Not Null));

StrSql =

Thanks

 

[Douglas J. Steele]

strSQL = "SELECT [FirstName] & "" "" & [LastName] AS FullName " & _
"FROM tblContacts INNER JOIN tblReservations ON tblContacts.ContactID " & _
"tblReservations.ResName " & _
"WHERE ((([FirstName] & "" "" & [LastName]) Is Not Null))"

Of course, [FirstName] & " " & [LastName] will ALWAYS be Not Null,
regardless of what the values are for [FirstName] and [LastName]!

That's because using & to concatenate a null value and a non-null (i.e. " ")
will always be non-null.

You may want to use

WHERE [FirstName] Is Not Null Or [LastName]) Is Not Null

 

[John Vinson]

How can I write this as a string SQL

SELECT [FirstName] & " " & [LastName] AS FullName
FROM tblContacts INNER JOIN tblReservations ON tblContacts.ContactID =
tblReservations.ResName
WHERE ((([FirstName] & " " & [LastName]) Is Not Null));

Double up on the quotemarks inside the quoted string:

StrSql = "SELECT [FirstName] & "" "" & [LastName] AS FullName " _
& "FROM tblContacts INNER JOIN tblReservations " _
& "ON tblContacts.ContactID = tblReservations.ResName " _
& "WHERE ((([FirstName] & "" "" & [LastName]) Is Not Null));"


John W. Vinson[MVP]

 

[poppybush]

Are you creating a SQL string for use in an ASP or ASP.NET web
application?

 

[Russ via AccessMonster.com]

Thanks, this worked perfect for me!!!

How can I write this as a string SQL

SELECT [FirstName] & " " & [LastName] AS FullName
FROM tblContacts INNER JOIN tblReservations ON tblContacts.ContactID =
tblReservations.ResName
WHERE ((([FirstName] & " " & [LastName]) Is Not Null));

Double up on the quotemarks inside the quoted string:

StrSql = "SELECT [FirstName] & "" "" & [LastName] AS FullName " _
& "FROM tblContacts INNER JOIN tblReservations " _
& "ON tblContacts.ContactID = tblReservations.ResName " _
& "WHERE ((([FirstName] & "" "" & [LastName]) Is Not Null));"

John W. Vinson[MVP]