SQL Expression

[Y Liu]

I am trying to write a SQL expression in a query that will return the full
name of a contact (ContFName + ContLName, or first name and last name) only
if the contact is the default contact for the company (or the
DefaultForCompany field is True).

Is this the correct SQL expression:

DefaultContName: ContFName & " " & ContLName if DefaultForCompany=True

 

[Sunny]

SQL Syntax :

SELECT TRIM(ContFName) + " " + ContLName AS DefaultContName FROM
yourtablename WHERE DefaultForCompany

 

[Y Liu]

Thanks for your reply.

However, I get the following error message when I use that query:
"The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses."

 

[Sunny]

Is your DefaultForCompany is Boolean field? If not change according to that
e.g. DefaultForCompany = 'Yes'

 

[Tim Anderson]

Tecnically, if you are using Trim, why not trim both first and last
names? Next, does it make sense to have first name first? If you are
concatenating name fields, most people would want to sort by last name.

SELECT Trim(ContLName) & ", " & Trim(ContFName) as ContactName
FROM yourtablename
WHERE DefaultForComapny = "Y"

Timothy R. Anderson
Millennium III Mgt Consulting
www.m-iii.org

 

[Sunny]

Lets say lastname is SMITH and firstname is JOHN and lenth of both field is
15 chars.

Now.

1. lastname & "," & firstname will return
SMITH ,JOHN

2. trim(lastname) & "," & trim(firstname) will return
SMITH,JOHN

3. trim(lastname) & "," & firstname will return
SMITH,JOHN

See in 2nd and 3rd it produce the same display result, but 2nd query has to
process one more trim() function, which take bit more time.