Pass Date Parameter

FlyBoy · Nov 23, 2004

I've searched through every newsgroup and Googled until my fingers are sore,
but haven't gotten any closer. Just dense I guess. I also apologize in
advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the proper
connections, I have an .asp page that I want to use for a query. The client
wants me to use 2 javascript pop-up calendars that fill dates into text
boxes. Using the values from these text boxes, I want to search for records
between a start and end date. Here's the SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:

);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(((IM_tblImpacts.ImpactDate) Between And ))'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for
all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get this to
work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>ClientName University Training Request</title>
<script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593" height="81"></p>
</div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date: </font>
<input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif" width="16"
height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif" width="16"
height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart"
s-columnnames="ImpactDate,ImpactType,ImpactingProject,ImpactedDepartment,ImpactDetails"
s-columntypes="135,202,202,202,203" s-dataconnection="X1"
b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue
b-tableborder="FALSE" b-tableexpand="TRUE" b-tableheader="TRUE"
b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0"
b-makeform="FALSE" s-recordsource
s-displaycolumns="ImpactDate,ImpactType,ImpactingProject,ImpactedDepartment,ImpactDetails"
s-criteria s-order s-sql="SELECT IM_tblImpacts.ImpactDate,
IM_tblImpacts.ImpactType, IM_tblImpacts.ImpactingProject,
IM_tblImpacts.ImpactedDepartment, IM_tblImpacts.ImpactDetails<br>FROM
IM_tblImpacts<br>WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
::EndDate:

);" b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="StartDate=&EndDate=" s-norecordsfound="No records returned."
i-maxrecords="256" i-groupsize="0" botid="0"
u-dblib="../_fpclass/fpdblib.inc" u-dbrgn1="../_fpclass/fpdbrgn1.inc"
u-dbrgn2="../_fpclass/fpdbrgn2.inc" tag="TBODY" preview="<tr><td colspan=64
bgcolor="#FFFF00" width="100%"><font color="#000000">This is the start of a
Database Results region.</font></td></tr>" startspan -->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:

);"
fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDepartment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody>
</table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

FlyBoy · Nov 23, 2004

Coorected the empty field error by specifying a date for each box, but that's
as far as I can get.

FlyBoy said:
I've searched through every newsgroup and Googled until my fingers are sore,
but haven't gotten any closer. Just dense I guess. I also apologize in
advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the proper
connections, I have an .asp page that I want to use for a query. The client
wants me to use 2 javascript pop-up calendars that fill dates into text
boxes. Using the values from these text boxes, I want to search for records
between a start and end date. Here's the SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator) in query expression '(((IM_tblImpacts.ImpactDate) Between And ))'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values for
all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get this to
work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>ClientName University Training Request</title>
<script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593" height="81"></p>
</div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date: </font>
<input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif" width="16"
height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif" width="16"
height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:);"
fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDepartment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr>
<td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody>
</table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Kathleen Anderson [MVP - FrontPage] · Nov 25, 2004

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND #::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

FlyBoy said:
I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I also
apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars that
fill dates into text boxes. Using the values from these text boxes, I
want to search for records between a start and end date. Here's the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
::EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression '(((IM_tblImpacts.ImpactDate)
Between And ))'. Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display database
content. The page must have a filename ending in '.asp', and the web
must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:);"
fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe
partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

FlyBoy · Nov 29, 2004

OK. Do you know a way around FrontPage immediately overwriting the change
when the page is saved? Microsoft has admitted this is a problem, and
suggests the same workaround. However, even in their KB article they don't
take into account this being overwritten. Creating a custom query and pasting
this in fails the verify against the database test.

Surely there must be some way to outsmart FP and get this done, right? (And

FlyBoy said:
The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND #::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

FlyBoy said:

I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I also
apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars that
fill dates into text boxes. Using the values from these text boxes, I
want to search for records between a start and end date. Here's the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
::EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression '(((IM_tblImpacts.ImpactDate)
Between And ))'. Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display database
content. The page must have a filename ending in '.asp', and the web
must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And ::EndDate:);"
fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe
partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Kathleen Anderson [MVP - FrontPage] · Nov 29, 2004

Yes.

In Code view, modify the SQL in the gray-colored code and save your changes
while still in Code view. You will notice that once you've saved your
changes they are also made to the maroon colored code - that is by design.
The maroon colored code should never be touched; it's generated by the gray
code at save time.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:
OK. Do you know a way around FrontPage immediately overwriting the
change when the page is saved? Microsoft has admitted this is a
problem, and suggests the same workaround. However, even in their KB
article they don't take into account this being overwritten. Creating
a custom query and pasting this in fails the verify against the
database test.

Surely there must be some way to outsmart FP and get this done,
right? (And on December 24th, a fat guy will come down my chimney and
leave me presents.)

Kathleen Anderson said:

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND
#::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

FlyBoy said:

I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I also
apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars that
fill dates into text boxes. Using the values from these text boxes,
I want to search for records between a start and end date. Here's
the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression '(((IM_tblImpacts.ImpactDate)
Between And ))'. Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display
database content. The page must have a filename ending in '.asp',
and the web
must be hosted on a server that supports Active Server
Pages.</div>"); </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
::EndDate:);" fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""

Click to expand...

fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe
partment=202&ImpactDetails=203&"

fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Click to expand...

FlyBoy · Nov 29, 2004

That worked great!

If I could bother you for one more thing, how do you set the default value
for the field so that all records are initially returned? Using * for a
default value obviously isn't the right answer.

Kathleen Anderson said:
Yes.

In Code view, modify the SQL in the gray-colored code and save your changes
while still in Code view. You will notice that once you've saved your
changes they are also made to the maroon colored code - that is by design.
The maroon colored code should never be touched; it's generated by the gray
code at save time.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:

OK. Do you know a way around FrontPage immediately overwriting the
change when the page is saved? Microsoft has admitted this is a
problem, and suggests the same workaround. However, even in their KB
article they don't take into account this being overwritten. Creating
a custom query and pasting this in fails the verify against the
database test.

Surely there must be some way to outsmart FP and get this done,
right? (And on December 24th, a fat guy will come down my chimney and
leave me presents.)

Kathleen Anderson said:

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND
#::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I also
apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars that
fill dates into text boxes. Using the values from these text boxes,
I want to search for records between a start and end date. Here's
the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax error
(missing operator) in query expression '(((IM_tblImpacts.ImpactDate)
Between And ))'. Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript" type="text/javascript"
src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font>
<input id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%">
<thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display
database content. The page must have a filename ending in '.asp',
and the web
must be hosted on a server that supports Active Server
Pages.</div>"); </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails FROM IM_tblImpacts WHERE
(((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
::EndDate:);" fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""

fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe
partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td> <td>
<%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit" name="B1"><input
type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Click to expand...

Kathleen Anderson [MVP - FrontPage] · Nov 29, 2004

Create a DRW with no criteria - it will display all the records in your
table.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:
That worked great!

If I could bother you for one more thing, how do you set the default
value for the field so that all records are initially returned? Using
* for a default value obviously isn't the right answer.

Kathleen Anderson said:

Yes.

In Code view, modify the SQL in the gray-colored code and save your
changes while still in Code view. You will notice that once you've
saved your changes they are also made to the maroon colored code -
that is by design. The maroon colored code should never be touched;
it's generated by the gray code at save time.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:

OK. Do you know a way around FrontPage immediately overwriting the
change when the page is saved? Microsoft has admitted this is a
problem, and suggests the same workaround. However, even in their KB
article they don't take into account this being overwritten.
Creating
a custom query and pasting this in fails the verify against the
database test.

Surely there must be some way to outsmart FP and get this done,
right? (And on December 24th, a fat guy will come down my chimney
and leave me presents.)

:

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND
#::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I
also apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars
that fill dates into text boxes. Using the values from these text
boxes,
I want to search for records between a start and end date. Here's
the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax
error (missing operator) in query expression
'(((IM_tblImpacts.ImpactDate) Between And ))'. Number:
-2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript"
type="text/javascript" src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18"
name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font> <input
id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%"> <thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display
database content. The page must have a filename ending in '.asp',
and the web
must be hosted on a server that supports Active Server
Pages.</div>"); </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate,
IM_tblImpacts.ImpactType, IM_tblImpacts.ImpactingProject,
IM_tblImpacts.ImpactedDepartment, IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts WHERE (((IM_tblImpacts.ImpactDate) Between
::StartDate:: And
EndDate:);" fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""

Click to expand...

fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe

partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Click to expand...

FlyBoy · Nov 30, 2004

Looks like that's what I'll have to do. Was trying to get the page to come up
with all the records showing initially, and then give the ability to filter
by query.

Thanks a million for your help Kathleen. I've said it before in here, but
you don't know how much we hacks appreciate your help. I know it saves me
countless hours of frustration (and certainly goes a long way towards keeping
my bar tab down!)

Kathleen Anderson said:
Create a DRW with no criteria - it will display all the records in your
table.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:

That worked great!

If I could bother you for one more thing, how do you set the default
value for the field so that all records are initially returned? Using
* for a default value obviously isn't the right answer.

Kathleen Anderson said:

Yes.

In Code view, modify the SQL in the gray-colored code and save your
changes while still in Code view. You will notice that once you've
saved your changes they are also made to the maroon colored code -
that is by design. The maroon colored code should never be touched;
it's generated by the gray code at save time.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

OK. Do you know a way around FrontPage immediately overwriting the
change when the page is saved? Microsoft has admitted this is a
problem, and suggests the same workaround. However, even in their KB
article they don't take into account this being overwritten.
Creating
a custom query and pasting this in fails the verify against the
database test.

Surely there must be some way to outsmart FP and get this done,
right? (And on December 24th, a fat guy will come down my chimney
and leave me presents.)

:

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND
#::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

I've searched through every newsgroup and Googled until my fingers
are sore, but haven't gotten any closer. Just dense I guess. I
also apologize in advance for this post being so verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars
that fill dates into text boxes. Using the values from these text
boxes,
I want to search for records between a start and end date. Here's
the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject, IM_tblImpacts.ImpactedDepartment,
IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax
error (missing operator) in query expression
'(((IM_tblImpacts.ImpactDate) Between And ))'. Number:
-2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No Records
Returned", even though there are records in the date range.

Using the wizards, different forms of code, etc, I just can't get
this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript"
type="text/javascript" src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4" color="#000080">
Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">

<p align="left"><b><font face="Tahoma" color="#000080">Start Date:
</font> <input id="start" type="text" size="18"
name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font> <input
id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%"> <thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The
Database Results component on this page is unable to display
database content. The page must have a filename ending in '.asp',
and the web
must be hosted on a server that supports Active Server
Pages.</div>"); </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate,
IM_tblImpacts.ImpactType, IM_tblImpacts.ImpactingProject,
IM_tblImpacts.ImpactedDepartment, IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts WHERE (((IM_tblImpacts.ImpactDate) Between
::StartDate:: And
EndDate:);" fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT"" width=""100%"">No
records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""

fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe
partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Click to expand...

Kathleen Anderson [MVP - FrontPage] · Nov 30, 2004

You're welcome

It's actually fun to see how far you can push the DBRW
without breaking it!

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

FlyBoy said:
Looks like that's what I'll have to do. Was trying to get the page to
come up with all the records showing initially, and then give the
ability to filter by query.

Thanks a million for your help Kathleen. I've said it before in here,
but you don't know how much we hacks appreciate your help. I know it
saves me countless hours of frustration (and certainly goes a long
way towards keeping my bar tab down!)

Kathleen Anderson said:

Create a DRW with no criteria - it will display all the records in
your table.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

FlyBoy said:

That worked great!

If I could bother you for one more thing, how do you set the default
value for the field so that all records are initially returned?
Using * for a default value obviously isn't the right answer.

:

Yes.

In Code view, modify the SQL in the gray-colored code and save your
changes while still in Code view. You will notice that once you've
saved your changes they are also made to the maroon colored code -
that is by design. The maroon colored code should never be touched;
it's generated by the gray code at save time.

--

~ Kathleen Anderson
Microsoft FrontPage MVP
Spider Web Woman Designs
http://www.spiderwebwoman.com/resources/

OK. Do you know a way around FrontPage immediately overwriting the
change when the page is saved? Microsoft has admitted this is a
problem, and suggests the same workaround. However, even in their
KB article they don't take into account this being overwritten.
Creating
a custom query and pasting this in fails the verify against the
database test.

Surely there must be some way to outsmart FP and get this done,
right? (And on December 24th, a fat guy will come down my chimney
and leave me presents.)

:

The date fields need pound signs:

WHERE ((IM_tblImpacts.ImpactDate) BETWEEN #::StartDate::# AND
#::EndDate::#)

--
~ Kathleen Anderson
Microsoft MVP - FrontPage
Spider Web Woman Designs
web: http://www.spiderwebwoman.com/resources/
blog: http://msmvps.com/spiderwebwoman/category/321.aspx

I've searched through every newsgroup and Googled until my
fingers are sore, but haven't gotten any closer. Just dense I
guess. I also apologize in advance for this post being so
verbose.

Using FP2003 on XPPro, with an Access 2003 database with all the
proper connections, I have an .asp page that I want to use for a
query. The client wants me to use 2 javascript pop-up calendars
that fill dates into text boxes. Using the values from these
text boxes,
I want to search for records between a start and end date.
Here's the
SQL statement:
SELECT IM_tblImpacts.ImpactDate, IM_tblImpacts.ImpactType,
IM_tblImpacts.ImpactingProject,
IM_tblImpacts.ImpactedDepartment, IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts
WHERE (((IM_tblImpacts.ImpactDate) Between ::StartDate:: And
EndDate:);

When I first open the page, I get:
Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Syntax
error (missing operator) in query expression
'(((IM_tblImpacts.ImpactDate) Between And ))'. Number:
-2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide default
values
for all form fields that are used in the query.

If I choose 2 dates and click the submit button, I get "No
Records Returned", even though there are records in the date
range.

Using the wizards, different forms of code, etc, I just can't
get this to work.

Page code:
<html>
<head>
<meta http-equiv="Content-Language" content="en-us">
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252"> <title>ClientName University Training
Request</title> <script language="javascript"
type="text/javascript" src="datetimepicker.js"></script>
</head>
<body>
<div align="center">
<table style= border="0" width="604" id="table1">
<tr>
<td>

<div style="border-style: ridge">
<p style="margin-top: 0; margin-bottom: 0" align="center">
<img border="0" src="images/ClientName.bmp" width="593"
height="81"></p> </div>
<div align="center">
<table border="0" width="598" id="table2">
<tr>
<td valign="top">
<p align="center"><b><font face="Tahoma" size="4"
color="#000080"> Query Test</font></b></p>
<p align="left"> </p>
<form method="POST" action="--WEBBOT-SELF--">
 <p align="left"><b><font
face="Tahoma" color="#000080">Start Date: </font> <input
id="start" type="text" size="18" name="StartDate"><a
href="javascript:NewCal('start','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a><br>
<font face="Tahoma" color="#000080"> End Date: </font> <input
id="end" type="text" size="18" name="EndDate"><a
href="javascript:NewCal('end','MMddyyyy')"><img src="cal.gif"
width="16" height="16" border="0" alt="Pick a date"></a></b></p>
<table width="100%"> <thead>
<tr>
<th ALIGN="LEFT"><b>ImpactDate</b></th>
<th ALIGN="LEFT"><b>ImpactType</b></th>
<th ALIGN="LEFT"><b>ImpactingProject</b></th>
<th ALIGN="LEFT"><b>ImpactedDepartment</b></th>
<th ALIGN="LEFT"><b>ImpactDetails</b></th>
</tr>
</thead>
<tbody>

<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color:
black;'>The Database Results component on this page is unable
to display database content. The page must have a filename
ending in '.asp', and the web
must be hosted on a server that supports Active Server
Pages.</div>"); </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT IM_tblImpacts.ImpactDate,
IM_tblImpacts.ImpactType, IM_tblImpacts.ImpactingProject,
IM_tblImpacts.ImpactedDepartment, IM_tblImpacts.ImpactDetails
FROM IM_tblImpacts WHERE (((IM_tblImpacts.ImpactDate) Between
StartDate:: And
EndDate:);" fp_sDefault="StartDate=&EndDate="
fp_sNoRecords="<tr><td colspan=5 align=""LEFT""
width=""100%"">No records returned.</td></tr>"
fp_sDataConn="X1"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""

Click to expand...

fp_sColTypes="&ImpactDate=135&ImpactType=202&ImpactingProject=202&ImpactedDe

partment=202&ImpactDetails=203&"
fp_iDisplayCols=5
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

<tr> <td>
<%=FP_FieldVal(fp_rs,"ImpactDate")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactType")%></td>
<td>
<%=FP_FieldVal(fp_rs,"ImpactingProject")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactedDepartment")%></td>
<td> <%=FP_FieldVal(fp_rs,"ImpactDetails")%></td>
</tr>

</tbody> </table>
<p align="left"> </p>
<p align="left"> </p>
<p align="left"><input type="submit" value="Submit"
name="B1"><input type="reset" value="Reset" name="B2"></p>
</form>
<p align="left"> </p>
<p align="left"> </p>
</td>
</tr>
</table>
</div>
</td>
</tr>
</table>
</div>

</body>

</html>

Click to expand...

Click to expand...

bullet list extra spacing	2	Jan 18, 2009
Database Results Wizard Error - FrontPage 2003	5	Mar 3, 2006
I am trying to understand this code...	3	Jul 26, 2008
Checkbox validation	4	May 19, 2008
Parameter passing query	4	Oct 27, 2004
Parameter passing within a ASP page	2	Oct 27, 2004
FP2003 SQL ignoring values passed through hyperlink	2	Apr 24, 2005
Database Results Error	2	Nov 24, 2004

Pass Date Parameter

FlyBoy

FlyBoy

Kathleen Anderson [MVP - FrontPage]

FlyBoy

Kathleen Anderson [MVP - FrontPage]

FlyBoy

Kathleen Anderson [MVP - FrontPage]

FlyBoy

Kathleen Anderson [MVP - FrontPage]

Ask a Question

Similar Threads