4
I want to enable a user to enter a start and end date to define the period they want to search for records of members who joined on certain dates. Funny thing is...I've got it to work half of the time. For e.g. I have 4 records between 26/10/2005 and 1/08/2006. When I enter 01/01/2005 as startDate and 31/08/2006 as endDate, I get the 4 records. However, if I alter the endDate to 01/09/2006 I get every record in the database!!!??? Why's this? I can't get my head around it!
Here's my code:
First the code for the form the user input the search criteria on:
<html>
<body>
<FORM action="memberJDateSearch.cfm" method="post">
<p>Start Date: <input type="text" name="startDate">
<br>End Date: <input type="text" name="endDate">
<input type="reset" value="Clear">
<input type="submit" value="Submit">
</FORM>
</body>
</html>
Simple enough. Now the code for the process and display page:
<html>
<body>
<cfquery name="memberJDateSearch" datasource="jpkelle2-access">
SELECT *
From members
WHERE ((joinDate BETWEEN #CreateODBCDate(startDate)# AND #CreateODBCDate(endDate)#))
</cfquery>
<table border=1 bgcolor="beige" cellpadding="3" cellspacing="0">
<tr>
<th>Member ID</th>
<th>Name</th>
<th>Sex</th>
<th>Date of Birth</th>
<th>Address</th>
<th>Email</th>
<th>Date Joined</th>
</tr>
<CFOUTPUT Query="memberJDateSearch">
<tr>
<td><center>#memberID#<center></td>
<td width="15%">#forename# #initial# #surname#</td>
<td>#sex#</td>
<td width="10%">#disp('#dob#')#</td>
<td>#address#, #town#, #county#, #postCode#</td>
<td>#email#</td>
<td width="10%">#disp('#joinDate#')#</td>
</tr>
</CFOUTPUT>
</table>
<hr><p>End of members list.</p>
</body>
</html>
any ideas? please help me.
