Pleeease help!!!

[paulw1982]

Hello,

I am having a nightmare of a time getting a caertain sql query to work

this is the statement...

SELECT USERINFO.USERNAME, OPPORTUNITY.DESCRIPTION, OPPORTUNITY.SALESPOTENTIAL, OPPORTUNITY.CLOSEPROBABILITY, OPPORTUNITY.ESTIMATEDCLOSE, PY3_OPPORTUNITY.EST_ORDER, PY3_OPPORTUNITY.MARGIN, ACCOUNT.ACCOUNT, USERINFO.DIVISION
FROM { oj ((Saleslogix.sysdba.OPPORTUNITY OPPORTUNITY LEFT OUTER JOIN Saleslogix.sysdba.ACCOUNT ACCOUNT ON OPPORTUNITY.ACCOUNTID = ACCOUNT.ACCOUNTID) LEFT OUTER JOIN Saleslogix.sysdba.USERINFO USERINFO ON OPPORTUNITY.ACCOUNTMANAGERID = USERINFO.USERID) LEFT OUTER JOIN Saleslogix.sysdba.PY3_OPPORTUNITY PY3_OPPORTUNITY ON OPPORTUNITY.OPPORTUNITYID = PY3_OPPORTUNITY.OPPORTUNITYID}
WHERE userinfo.username = &quot;Petrona O'Neill&quot; AND (OPPORTUNITY.CLOSEPROBABILITY >= 10 AND OPPORTUNITY.CLOSEPROBABILITY <= 90) AND OPPORTUNITY.ESTIMATEDCLOSE >= CONVERT(smalldatetime, '&quot;+Request.Form(&quot;fromdate&quot+&quot;', 103) AND OPPORTUNITY.ESTIMATEDCLOSE <= CONVERT(smalldatetime, '&quot;+Request.Form(&quot;todate&quot+&quot;', 103)
ORDER BY OPPORTUNITY.ESTIMATEDCLOSE ASC

The problem is here> WHERE userinfo.username = &quot;Petrona O'Neill&quot;

The apostrophe in the name is causing an error.

Please can somebody tell me how to write that part of the statement to get it to work properly.

Thanks

 

[luds]

I'm no expert on this language.. but I remember reading something about &quot;escaping&quot; the character.. think if you put a \ before the ' then it's interpreted as an apostrophe.

Could be wrong, mind!

luds

http://www.nationalmozart.com

http://www.cezanne-ensemble.co.uk

 

[espar]

Yeah you can escape like that for a *nix system

Also for varchars / text in sql you delimit it by ' not &quot;

So you would need

= 'Petrona O''Neill'

ie. double single quotes - have fun...

 

[bjm027]

If you are using Microsoft Access, I do beleive that Access doesn't accept ('). Try using this function.

--------------------------
Function Change(strDesc)
'This function replace a single apostrophe with a ^ so the field can be saved to the database.
Change = strDesc
Change = Replace(Change, &quot;'&quot;, &quot;~&quot
End Function
--------------------------

 

[vbkris]

WHERE userinfo.username = &quot;Petrona O'Neill&quot;

the &quot; is the problem, i dont think u can use &quot; in access, this must work:

WHERE userinfo.username = 'Petrona ONeill'

Note: i have removed the ' in McNiell, u have to remove it and place a special character...




Known is handfull, Unknown is worldfull

 

[Cheech]

If it must be a single quote, try building it like this

strUserName = "Petrona O'Neill"
WHERE userinfo.username = strUsername AND

Cheech

[Peace][Pipe]
If you don't stand up for something, you'll fall down. Toke it Easy.
Howard Marks. Visit the forum at

http://mrnice.net