Hi Julia, I went to your search screen and tried returning some data using the following dates:
5/6/1999 AND 12/6/2002 It couldn't find anything, so it was a bit hard to reproduce your problem. However, this query will not return anything except the year you ask for: <CFQUERY NAME="gilwayc" DATASOURCE="cgi2"> SELECT count(t.File) as file3, t.Clock, g.File, t.File FROM tblfile4d t, gilway12 g WHERE t.File=g.File AND (Clock BETWEEN #CreateODBCDate(Form.Clock1)# AND #CreateODBCDate(Form.Clock2)#) GROUP BY g.File, t.File, t.Clock ORDER BY file3 DESC </CFQUERY> You also might want to put some validation in your search form to make sure your site visitors only enter valid dates. I put in 77/6/1999 and it allowed the page to process... Hope this helps ! ;) Peter Bagnato -----Original Message----- From: Julia Green [mailto:[EMAIL PROTECTED]] Sent: Sunday, December 29, 2002 2:47 PM To: CF-Talk Subject: ODBC Date Question This I am sure is a beginning question...so one of you experts can help me... I have a client Gilway lighting, whose form, gilwaydb.cfm submits an ODBC date <CFINPUT TYPE="TEXT" NAME="Clock" SIZE="10" VALUE=#CreateODBCDate(Now())#> to an Access table whose dates I converted all to Date/Time(shot over to SQL Server) in the format mm/dd/yyyy, the table is called tblfile4d Now the problem, my client wants me to do a form where you put a Beginning and Ending Date, Beginning Date:<br> <CFINPUT TYPE="text" NAME="Clock1" SIZE="20"><br> Ending Date:<br> <CFINPUT TYPE="text" NAME="Clock2" SIZE="20"><br> <P><CENTER>Type the dates in the following format: 10/13/2001. Be sure to type a beginning and ending date.<BR> <p><INPUT TYPE="submit"VALUE="Search"></CENTER></P> and to find the files that go with these dates, but for instance, if I enter 5/6/01 or 5/6/2001 and 12/6/01, it returns the 02 dates, all between 5/6/2002 and 12/6/2002. So remember tblfile4d has date format for Clock for all the dates now. It is called gilsrch.cfm posts to gildate2.cfm with this relevant code: <CFQUERY NAME="gilwayc" DATASOURCE="cgi2"> SELECT count(t.File) as file3, t.Clock, g.File, t.File FROM tblfile4d t, gilway12 g WHERE Clock BETWEEN #CreateODBCDate(Form.Clock1)# AND #CreateODBCDate(Form.Clock2)# AND t.File=g.File GROUP BY g.File, t.File, t.Clock ORDER BY file3 DESC </CFQUERY> <CFQUERY NAME="GetQuery" DATASOURCE="cgi2"> SELECT count(File) AS file2 FROM tblfile4d WHERE Clock BETWEEN #CreateODBCDate(Form.Clock1)# AND #CreateODBCDate(Form.Clock2)# </cfquery> <CFQUERY NAME="GetQueryc" DATASOURCE="cgi2"> SELECT count(t.File) as file4 FROM tblfile4d t, gilway12 g WHERE Clock BETWEEN #CreateODBCDate(Form.Clock1)# AND #CreateODBCDate(Form.Clock2)# AND t.File=g.File </cfquery> Why is it doing this I can give you the links... http://www.cheshiregroup.com/gilway/gilwaydb.cfm http://www.cheshiregroup.com/gilway/gilsrch.cfm Can someone help me with this? Why does it return other years? Please cc me at [EMAIL PROTECTED] if you can Julia Green Julia Computer Consulting Web Design @ Reasonable Prices Email: [EMAIL PROTECTED] www.juliagreen.com Phone: 617-926-3413 Cell: 617-596-6003 Fax: 1-617-812-8148 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com
