navigation
a webmaster learning community
     Home    Register     Search      Help      Login    
Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums
 

Advanced search
Recent Posts

 Todays Posts
 Most Active posts
 Posts since last visit
 My Recent Posts
 Mark posts read

 

date 1 to date 2 query

 
View related threads: (in this forum | in all forums)

Logged in as: Guest
Users viewing this topic: none
Printable Version 

All Forums >> Web Development >> ASP and Database >> date 1 to date 2 query 		Page: [1]
 
Meni

 

Posts: 29
Joined: 8/3/2005
Status: offline

  		date 1 to date 2 query - 11/10/2005 6:53:46   
I am going crazy.

Created a database website with FP2003.
table name = Results.

The Access DB stores customer info, and among other things - it stores their date of birth.
the Field name = dateOfBirth
the format is - dd/mm/yyyy

I want to create a form in my ASP site, with two text boxes.
startd = start date
endd = end date

I want the user to input a date range and to return all values from "Results" that are within that date range.

The query I am creating looks like this:

SELECT * FROM Results Where dateOfBirth Between ::startd:: And :endd::

all I get is 'The custom query contains errors'

ANy help would be much appreciated.
BeTheBall

 

Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline

  		RE: date 1 to date 2 query - 11/10/2005 8:36:52   
Does this work?

SELECT * FROM Results Where dateOfBirth Between #::startd::# And #::endd::#

_____________________________

Duane

Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.

(in reply to Meni)
Meni

 

Posts: 29
Joined: 8/3/2005
Status: offline

  		RE: date 1 to date 2 query - 11/10/2005 8:58:24   
NO.

Pasting this code into FP is out of the question because it says there's an error.
So I manually paste it using notepad.

This is what I get:

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT * FROM Results Where dateOfBirth Between #::startd::# And #::endd::#




(in reply to BeTheBall)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: date 1 to date 2 query - 11/10/2005 11:02:26   
quote:

it stores their date of birth


Is dateOfBirth a Date/Time field?

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to Meni)
Meni

 

Posts: 29
Joined: 8/3/2005
Status: offline

  		RE: date 1 to date 2 query - 11/10/2005 14:06:58   
yes. it is a date/time field.

(in reply to rdouglass)
Meni

 

Posts: 29
Joined: 8/3/2005
Status: offline

  		RE: date 1 to date 2 query - 11/10/2005 19:45:24   
Below - is my code.
It returns no errors - but searching returns false data.
for example - i could search for 10/10/2005 -> 12/10/2005 , and 13/10/2005 would still show up as a result.

HELP! 

<body bgcolor="#FFFFFF" background="../../../images/bg.gif">
<!--#include file="head.inc"-->

<form method="POST" action="--WEBBOT-SELF--">
	<!--webbot bot="SaveResults" U-File="../../../_private/form_results.csv" S-Format="TEXT/CSV" S-Label-Fields="TRUE" startspan --><input TYPE="hidden" NAME="VTI-GROUP" VALUE="0"><!--webbot bot="SaveResults" i-checksum="43374" endspan -->
	<p><input type="date" name="startd" size="20"></p>
	<p><input type="text" name="endd" size="20"></p>
	<table width="100%" border="1">
		<thead>
			<tr>
				<th ALIGN="LEFT"><b>Key</b></th>
				<th ALIGN="LEFT"><b>firstName</b></th>
				<th ALIGN="LEFT"><b>lastName</b></th>
				<th ALIGN="LEFT"><b>sex</b></th>
				<th ALIGN="LEFT"><b>address</b></th>
				<th ALIGN="LEFT"><b>homePhone</b></th>
				<th ALIGN="LEFT"><b>mobilePhone</b></th>
				<th ALIGN="LEFT"><b>product</b></th>
				<th ALIGN="LEFT"><b>dateOfBirth</b></th>
				<th ALIGN="LEFT"><b>colorType</b></th>
				<th ALIGN="LEFT"><b>color1</b></th>
				<th ALIGN="LEFT"><b>color2</b></th>
				<th ALIGN="LEFT"><b>color3</b></th>
				<th ALIGN="LEFT"><b>oxygen</b></th>
				<th ALIGN="LEFT"><b>notes</b></th>
			</tr>
		</thead>
		<tbody>
			<!--webbot bot="DatabaseRegionStart" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-columntypes="3,202,202,202,202,202,202,202,135,202,202,202,202,202,202" s-dataconnection="db" b-tableformat="TRUE" b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE" b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE" b-listseparator="TRUE" i-listformat="0" b-makeform="FALSE" s-recordsource="Results" s-displaycolumns="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-criteria="[dateOfBirth] GT {startd} + [dateOfBirth] NGT {endd} +" s-order s-sql="SELECT * FROM Results WHERE (dateOfBirth >  '::startd::' AND dateOfBirth <=  '::endd::')" b-procedure="FALSE" clientside suggestedext="asp" s-defaultfields="startd=1/1/1900&endd=12/12/2100" s-norecordsfound="אין ערכים..." 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. The page must be fetched from a web server with a web browser to display correctly; the current web is stored on your local disk or network.</font></td></tr>" startspan --><!--#include file="../../../_fpclass/fpdblib.inc"-->
<% 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 * FROM Results WHERE (dateOfBirth >  '::startd::' AND dateOfBirth <=  '::endd::')"
fp_sDefault="startd=1/1/1900&endd=12/12/2100"
fp_sNoRecords="<tr><td colspan=15 align=""LEFT"" width=""100%"">אין ערכים...</td></tr>"
fp_sDataConn="db"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&Key=3&firstName=202&lastName=202&sex=202&address=202&homePhone=202&mobilePhone=202&product=202&dateOfBirth=135&colorType=202&color1=202&color2=202&color3=202&oxygen=202¬es=202&"
fp_iDisplayCols=15
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../../../_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="33254" --><tr>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="Key" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>Key<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"Key")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="2510" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="firstName" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>firstName<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"firstName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="20760" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="lastName" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>lastName<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"lastName")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="16465" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="sex" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>sex<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"sex")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="5069" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="address" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>address<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"address")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="14151" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="homePhone" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>homePhone<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"homePhone")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="21943" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="mobilePhone" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>mobilePhone<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"mobilePhone")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="33286" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="product" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>product<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"product")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="16838" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="dateOfBirth" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>dateOfBirth<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"dateOfBirth")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="31591" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="colorType" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>colorType<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"colorType")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="21842" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="color1" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>color1<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"color1")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="17023" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="color2" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>color2<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"color2")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="17031" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="color3" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>color3<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"color3")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="17039" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="oxygen" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>oxygen<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"oxygen")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="19376" --></td>
				<td>
				<!--webbot bot="DatabaseResultColumn" s-columnnames="Key,firstName,lastName,sex,address,homePhone,mobilePhone,product,dateOfBirth,colorType,color1,color2,color3,oxygen,notes" s-column="notes" b-tableformat="TRUE" b-hashtml="FALSE" b-makelink="FALSE" clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>notes<font size="-1">&gt;&gt;</font>" startspan --><%=FP_FieldVal(fp_rs,"notes")%><!--webbot bot="DatabaseResultColumn" endspan i-checksum="11247" --></td>
			</tr>
			<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE" b-menuformat="FALSE" u-dbrgn2="../../../_fpclass/fpdbrgn2.inc" i-groupsize="0" clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00" width="100%"><font color="#000000">This is the end of a Database Results region.</font></td></tr>" startspan --><!--#include file="../../../_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="9297" --></tbody>
	</table>
	<p> </p>
	<p><input type="submit" value="   שלח   " name="B1"></p>
</form>
<script language="Javascript1.2">
<!--

var message = "שלח להדפסה";

function printpage() {
window.print();  
}

document.write("<form><input type=button "
+"value=\""+message+"\" onClick=\"printpage()\"></form>");

//-->
</script>

</body>

</html>

(in reply to Meni)
rdouglass

 

Posts: 9280
From: Biddeford, ME USA
Status: offline

  		RE: date 1 to date 2 query - 11/11/2005 8:52:23   
quote:

it is a date/time field


OK. Since it is Access and a Date/Time field, the date is being stored in the mm/dd/yyyy format. You'll either have to enter your dates in that format or you'll have to convert before you do the search.

_____________________________

Don't take you're eye off your final destination.

ASP Checkbox Function Tutorial.

(in reply to Meni)
Meni

 

Posts: 29
Joined: 8/3/2005
Status: offline

  		RE: date 1 to date 2 query - 11/16/2005 9:39:45   
thanks rdouglas! that's what i was looking for!


(in reply to rdouglass)
ashumak

 

Posts: 1
Joined: 11/20/2005
Status: offline

  		RE: date 1 to date 2 query - 11/20/2005 21:43:35   
I don't mean to bud in, but I need to do the same thing in my FP site. Any place I can find step by step instructions to build such a query page.

Alan

(in reply to rdouglass)
Page:   [1]

All Forums >> Web Development >> ASP and Database >> date 1 to date 2 query 		Page: [1]
Jump to: 1





New Messages No New Messages
Hot Topic w/ New Messages Hot Topic w/o New Messages
Locked w/ New Messages Locked w/o New Messages
 Post New Thread
 Reply to Message
 Post New Poll
 Submit Vote
 Delete My Own Post
 Delete My Own Thread
 Rate Posts


Back to top

  Microsoft Corporation in no way endorses or is affiliated with "OutFront."
  OutFront is published and edited by Thomas Brunt.
  440 Great Circle Dr., St. Matthews, SC 29135.
  © 2008 All Rights Reserved
  Click here to view our privacy statement.