How do you validate the input dates with isdate()? Never used it before... Julia Richard Meredith-Hardy <[EMAIL PROTECTED]> wrote:A few notes:
- is 'File' a reserved word? - I have to say a a European I HATE date formats like mm/dd/yyyy or dd/mm/yyyy for the simple reason that there are too many ways to misunderstand them - when I ask people to put a date in a single field I ask them to use dd mmm yyyy which is completely unambiguous and is perfectly understood by createodbcdate() - are you sure the actual values of the dates in your new DB are what you think they are after the transformation? - validate the input dates before the query with isdate() Julia Green wrote: > > 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 > > 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: > > Ending Date: > > > Type the dates in the following format: 10/13/2001. Be sure to type a beginning and ending date. > [input] > 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: > > > 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 > > > > > SELECT count(File) AS file2 > FROM tblfile4d > WHERE Clock BETWEEN #CreateODBCDate(Form.Clock1)# AND #CreateODBCDate(Form.Clock2)# > > > 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 > > > 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 Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm