I've created an ActiveX Script that's located within a DTS package to export database table data into an XML file. It works great, but now I need to filter the data to be exported a bit.The first setup was to get all "archived" records by using the "end_date" and current date, like this:'**********************************************************************' Visual Basic ActiveX Script'************************************************************************Function Main() Dim objADORS Dim objXMLDoc Dim nodeRoot Dim nodeTemp Dim nodeRelease 'Create ADO and MSXML DOMDocument Objects Set objADORS = CreateObject("ADODB.Recordset") Set objXMLDoc = CreateObject("MSXML2.DOMDocument.3.0") 'Run the stored procedure and load the Recordset objADORS.Open "SELECT * FROM tblNewsReleases_test", _ "PROVIDER=SQLOLEDB.1;SERVER=SERVERNAME;UID=UID;PWD=PWD;DATABASE=DBNAME;" 'Prepare the XML Document objXMLDoc.loadXML "<root />" Set nodeRoot = objXMLDoc.documentElement 'For each record in the Recordset While Not objADORS.EOF and objADORS.Fields("item_date").Value < date() Set nodeRelease = objXMLDoc.createElement("release") nodeRoot.appendChild nodeRelease Set nodeTemp = objXMLDoc.createElement("release_id") nodeTemp.nodeTypedValue = Trim(objADORS.Fields("release_id").Value) nodeRelease.appendChild nodeTemp Set nodeTemp = objXMLDoc.createElement("start_date") nodeTemp.nodeTypedValue = Trim(objADORS.Fields("start_date").Value) nodeRelease.appendChild nodeTemp Set nodeTemp = objXMLDoc.createElement("end_date") nodeTemp.nodeTypedValue = Trim(objADORS.Fields("end_date").Value) nodeRelease.appendChild nodeTemp Set nodeTemp = objXMLDoc.createElement("title") nodeTemp.nodeTypedValue = Trim(objADORS.Fields("title").Value) nodeRelease.appendChild nodeTemp Set nodeTemp = objXMLDoc.createElement("information") nodeTemp.nodeTypedValue = Trim(objADORS.Fields("information").Value) nodeRelease.appendChild nodeTemp objADORS.moveNext Wend objADORS.Close Set objADORS = Nothing 'Save the created XML document objXMLDoc.Save "B:\XMLEXPORTFILE.xml" Main = DTSTaskExecResult_SuccessEnd Function
But when I try a similar setup to get all records in which the current date is between the "start date" and "end_date" of the record like this, no records come up:...While Not objADORS.EOF and objADORS.Fields("start_date").Value < date() and objADORS.Fields("end_date").Value > date()...
I was easily able to do this in SQL Server using this query:SELECT *FROM tblNewsReleases_testWHERE (start_date < GETDATE()) AND (end_date > GETDATE())...so I know that I have the right idea. I'm just not sure what I'm doing wrong, and I'm hoping that you can help me out. If anyone can see where I'm making a mistake with the second setup, it would be very helpful. Thanks.KWilliams-------------------It's the end of the world as we know it...and I feel fine