Yes, it does look like a overcomplicated query. However why would you
need to convert them to dates, the only reason I could think of is if you
are actually inserting times with those dates. I see no reason as to why
this would not return the same results as what you are trying:
SELECT * FROM classes WHERE ID = #Form.ClassSel#
If you are not entering in a time with the date then doesn't Access allow
this to be a proper join as well:
SELECT *
FROM classes
WHERE ClassDate = (SELECT ClassDate FROM classes
WHERE id=#Form.ClassSel#)
Snipe - <CF_BotMaster Network="EFNet" Channel="ColdFusion">
On Wed, 6 Feb 2002, Dina Hess wrote:
> bruce,
>
> looks to me like you're overcomplicating your query. if you're trying to
> 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]
> -------------------------------------------------
>
>
>
>
______________________________________________________________________
Why Share?
Dedicated Win 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=coldfusionc
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
