You missed some key info I mentioned. Access stores dates as date and ti me (1/6/2002 1:57:00 PM). In my table are classes for the same day, but at different times. What I want my query to find (and maybe what wasn't clear) is all classes for a given date (just the date part, not the time, otherwise it will ret urn just one class matching the date and time) where the id for one of the classes is "ClassSel" (the id of the class the user has specified from a pull down menu.) What I need is to determine the DATE portion of the field, ignoring the T IME portion of the same field.
BTW, when I created the date in Access, I specified the Date format to be General which stores date and time info in the SAME field. Any new tries at helping me? ----- Original Message ----- From: "Dina Hess" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, February 06, 2002 10:18 AM Subject: Re: Formatting a Date in MS SQL Access Query...Try Again > bruce, > > looks to me like you're overcomplicating your query. if you're trying t o > retrieve records in the classes table where id = #form.classsel# your > query would be "select * from classes where id = #form.classsel#". i > don't really understand why you need the date comparison, so maybe i'm > missing something. btw, you insert odbc access dates like this: "insert > into classes (classdate) values > (#createodbcdate(createdate(2002,02,06))#)" and output them like this: > #dateformat(query_name.classdate,"mmmm d, yyyy")#. > > hth > --dina > > > > > ----- Original Message ----- > From: Bruce Holm > To: CF-Talk > Sent: Wednesday, February 06, 2002 11:57 AM > Subject: Formatting a Date in MS SQL Access Query...Try Again > > > I'm trying to format a date within a SQL query against an Access > database. > > In Oracle you can say TO_DATE(date,mask) but that function doesn't > exist > > in Access. What function is there in Access to do this? > > Here is the query that doesn't work: > > <cfquery datasource="abb" name="qSelClassInfo"> > SELECT * > FROM classes > WHERE TO_DATE(ClassDate,'mm/dd/yy') = TO_DATE(( > SELECT ClassDate FROM classes > WHERE id=#Form.ClassSel#),'mm/dd/yy') > </cfquery> > > Note that Access stores date and time info in the same field. So I'm > trying to compare just the date portion since several classes can > occur > on the same day. > > > ------------------------------------------------- > Bruce Holm - Web Programmer > Lattice Semiconductor Corp. > [EMAIL PROTECTED] > ------------------------------------------------- > > > > ______________________________________________________________________ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
