error undefined function

[Paul Mayer]

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.

 

[Greg Collins]

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.

 

[Paul Mayer]

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..

 

[Greg Collins]

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.