OK - I have HAD IT with this problem <img src=../images/mxzone/forum/icon_smile_sad.gif border=0 align=middle>

Firstly I am using DMX, ASP JavaScript and MySQL.

I have been trying to create an ASP page which only displays records that are between a "startDate" and "endDate". I can do the SQL which works fine, but as soon as I try to make both the date values dynamic (populated from another database recordset), i get errors all over the place.

Here is the SQL I use in my recordset for what I want to achieve:

SELECT * FROM lic_main_table
WHERE date_of_birth_applicant_1
BETWEEN '1900-07-22 00:50:46' AND '2002-07-22 00:50:46'

Now, this works fine. BUT, the two dates are static. I want them to come from one of my recordsets (dates).

I thought it should look like this:

SELECT * FROM lic_main_table
WHERE date_of_birth_applicant_1
BETWEEN '(dates.Fields.Item("startDate".Value)' AND '(dates.Fields.Item("startDate".Value)'

But all this does is returns an SQl error.

In short, I have not got any further forward. I cannot display records between two dates using ASP.

Has anyone done this before?

Hope you cam help <img src=../images/mxzone/forum/icon_smile_big.gif border=0 align=middle>

Thanks a million,
Gary.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I thought it should look like this:

SELECT * FROM lic_main_table
WHERE date_of_birth_applicant_1
BETWEEN '(dates.Fields.Item("startDate".Value)' AND '(dates.Fields.Item("startDate".Value)'

But all this does is returns an SQl error.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>

Well if the dates are to come from another recordset then pass the values into a string:

---------------------------
sStartDate = rsDate.Fields.Item("StartDate".Value
sEndDate = rsDate.Fields.Item("EndDate".Value
---------------------------

Then in your SQL string for the next recordset:

---------------------------
sSQL = "Select * from tblDates where date BETWEEN '" & sStartDate & "' AND '" & sEndDate & "'"
---------------------------

This means that the values will not be static as they will come from your first recordset.

Hope this helps a bit

********************
wheatNOTmeat

Got it working at last!!! Thanks for the effort <img src=../images/mxzone/forum/icon_smile.gif border=0 align=middle>

Most of this generated by DMX.

Gary.



Working Version:
&lt;%
var Recordset1__MMColParam = "1";
if (String(Request.QueryString("startdate") != "undefined" &&
String(Request.QueryString("startdate") != "" {
Recordset1__MMColParam = String(Request.QueryString("startdate");
}
%&gt;
&lt;%
var Recordset1__MMColParam2 = "1";
if (String(Request.QueryString("enddate") != "undefined" &&
String(Request.QueryString("enddate") != "" {
Recordset1__MMColParam2 = String(Request.QueryString("enddate");
}
%&gt;
&lt;%
var Recordset1 = Server.CreateObject("ADODB.Recordset";
Recordset1.ActiveConnection = MM_licdates_STRING;
startDate = "123";
Recordset1.Source = "SELECT * FROM lic_main_table WHERE timestamp BETWEEN '"+ Recordset1__MMColParam.replace(/'/g, "''" + "' and '"+ Recordset1__MMColParam2.replace(/'/g, "''" + "'";
Recordset1.CursorType = 0;
Recordset1.CursorLocation = 2;
Recordset1.LockType = 1;
Recordset1.Open();
var Recordset1_numRows = 0;
%&gt;
&lt;%
var Repeat1__numRows = -1;
var Repeat1__index = 0;
Recordset1_numRows += Repeat1__numRows;
%&gt;