How to make a Test String if SQL has "" in it?

[tfayer]

I have SQL Statement for a query:

SELECT Trim([p_name]) & IIf([p_name]<>&quot;&quot;,&quot; &quot & Trim([f_name]) & IIf([f_name]<>&quot;&quot;,&quot; &quot & Trim([m_name]) & IIf([m_name]<>&quot;&quot;,&quot; &quot & Trim([l_name]) & IIf([s_name]<>&quot;&quot;,&quot;, &quot & Trim([s_name]) AS Expr1, tblContacts.company, tblContacts.title, tblContacts.tollfree, tblContacts.email, tblContacts.business_phone1, tblContacts.business_ext1, tblContacts.business_phone2, tblContacts.business_ext2, tblContacts.business_fax
FROM tblContacts;

I have done Dim stSQL as String

and

stSQL = &quot;All the above statement&quot;

But, it keeps giving me Expected end of statement and highlighting atea around quotations in the Trim part.

Any suggestions.

 

[DanJR]

Hi there,

your SQL statement has double quote in it which is interfering with the way strings are referenced in code. The easiest way to get around this is to replace all the double quotes with single quotes.

e.g.
SELECT Trim([p_name]) & IIf([p_name]<>'',' ')

Another way is to add an extra double quote for each double quote that appears in your sql string

e.g.

str = &quot;SELECT Trim([p_name]) & IIf([p_name]<>&quot;&quot;&quot;&quot;,&quot;&quot; &quot;&quot&quot;

Hope this helps
Cheers,
Dan

 

[danvlas]

First:
IIf function has three parts:

IIf({Condition}, {Result If True}, {Result If False})

Your IIf function has only two parts.

I guess you want to concatenate FirstName with last name, keeping a space between them

You could use

Expression: LTrim([p_name] & &quot; &quot & LTrim([l_name] & &quot; &quot & LTrim([f_name] & &quot; &quot

If [p_name] is null or zero length string or spaces, the LTrim will strip all spaces and you'll get a zero length string. Finally, you'll get all existing values concatenated and a trailing space.

HTH


Daniel Vlas
Systems Consultant
danvlas@yahoo.com