error undefined function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul Mayer - 12 Mar 2008 17:07 GMT

I have the following SQL statement:

TRANSFORM IIf(IsNull([StatusCd]),0,Count([leadno])) AS Expr1
SELECT Leads.ABSCd, Count(Leads.LeadNo) AS total
FROM Leads
WHERE
Leads.CmpltdDt>=XDocument.DOM.selectSingleNode("my:MyFields/my:StartMe") And
Leads.CmpltdDt<=XDocument.DOM.selectSingleNode("my:MyFields/my:StopMe")
GROUP BY Leads.ABSCd
ORDER BY Leads.ABSCd
PIVOT Leads.StatusCd;

When I paste this SQL into the data connection, I get an error undefined
function
'XDocument.DOM.selectSingleName' in expression
is it possible to use XDocument.DOM.selectSingleNode in a SQL statement if
so how.

Reply to this Message

Greg Collins - 12 Mar 2008 17:57 GMT

You need to build your SQL string first, by concatenating the SQL code with
the values from your selectSingleNode statements.

Then set your data connection with the completed SQL string.

Signature

Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com
Visit InfoPathDev at http://www.infopathdev.com

Reply to this Message

Paul Mayer - 12 Mar 2008 19:05 GMT

Okay, I built the Dynamic SQL Statment using the concat function in a text
varible MySQL(I understand the conecpt of Dynamic SQL. I have several follow
up questions now do I set the data connection with the completed sql string?
Do you mean paste the concat statement into the where I edit the sql
statement in the data connection wizard or some other methiod of changing the
data connection? Does it matter whether the date is in the format 2008-03-01
when it is passed to the back end access data tables or does it need to be
formated 03/01/2008 for it to return back results? BTW, I am using IP 2003
nad Access 2003 data table for my data connection..

> You need to build your SQL string first, by concatenating the SQL code with
> the values from your selectSingleNode statements.
>
> Then set your data connection with the completed SQL string.

Reply to this Message

Greg Collins - 18 Mar 2008 00:33 GMT

Q: How do I set the data connection with the completed sql string?

A: You are using code. There is a property in your data connection where you
can assign the updated SQL statement.

Q: Does it matter whether the date is in the format 2008-03-01 when it is
passed to the back end access data tables or does it need to be formated
03/01/2008 for it to return back results?

A: No. SQL will accept the sortable xml date format: 2008-03-01.

Signature

Greg Collins
Microsoft MVP
Visit Braintrove at http://www.braintrove.com
Visit InfoPathDev at http://www.infopathdev.com

Reply to this Message

Rate this thread: