[cfaussie] RE: New look up date by years old with a 2 year sprea d < 2 > 2

WALTERS Scott Wed, 19 Mar 2003 13:17:35 -0800

Greg,

Loose the <cfoutput></cfoutput> and <pre></pre> tags, only SQL syntax should go between cfquery tags. For a good description look at http://livedocs.macromedia.com/cfmxdocs/CFML_Reference/Tags-pt218.jsp#1102316

Also, change the WHERE clause to valid SQL syntax, I'm pretty sure BETWEEN x AND y isn't valid, though I may be wrong.

IE:

<cfquery name="getRecord" datasource="interact">
SELECT Talent.talentID ,
  Talent.Name ,
  Talent.Gender ,
  Talent.DOB ,
  Talent.Hair ,
  Talent.Eye ,
  Talent.Height ,
  Talent.Clothessize ,
  Talent.shoesize ,
  Talent.hobbies ,
  Talent.BIO ,
  Talent.Compcard ,
  Talent.ID
FROM Talent
WHERE Talent.DOB > ('#Olderthan#') AND Talent.DOB < ('#Youngerthan#')

</cfquery>

Good luck.

Scott.

-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 19 March 2003 10:09 PM
To: CFAussie Mailing List
Subject: [cfaussie] New look up date by years old with a 2 year spread < 2 > 2

Hi

thank you for all who helped yesterday

i am still stuck so i decided to clean up and simplfy the code to help debug

here we go again:

<cfset Form.age =5>

<cfset Older = Form.age + 2>
<cfset Younger = Form.age - 2>


<cfoutput>
<cfset Olderthan = #CreateODBCDateTime(DateAdd("yyyy", -Older, Now()))#><br>
<cfset Youngerthan = #CreateODBCDateTime(DateAdd("yyyy",-Younger,Now()))#>
get all records where member was born between #Olderthan# and #Youngerthan#
</cfoutput>

<cfquery name="getRecord" datasource="interact">
<cfoutput><pre>
SELECT Talent.talentID ,
  Talent.Name ,
  Talent.Gender ,
  Talent.DOB ,
  Talent.Hair ,
  Talent.Eye ,
  Talent.Height ,
  Talent.Clothessize ,
  Talent.shoesize ,
  Talent.hobbies ,
  Talent.BIO ,
  Talent.Compcard ,
  Talent.ID
FROM Talent
where Talent.DOB between ('#Olderthan#') and ('#Youngerthan#')
</pre></cfoutput>
</cfquery>

get all records where member was born between {ts '1996-03-19 22:06:56'} and {ts '2000-03-19 22:06:56'}

The ERROR MESSAGE

<FONT size=3></PLAINTEXT><PRE></PRE></Q></S></SAMP>></SMALL></STRIKE></STRONG></SUB></SUP></TABLE></TD></TH></TITLE></TR></TT></U> <UL></UL></VAR></WBR><XMP></XMP> <HR> <H3>Error Occurred While Processing Request</H3> <P> <TABLE border=1> <TBODY> <TR> <TD> <H4>Error Diagnostic Information</H4> <P>ODBC Error Code = 37000 (Syntax error or access violation) <P> <P>[Microsoft][ODBC Microsoft Access Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. <P> <P>SQL = "<PRE> SELECT Talent.talentID , Talent.Name , Talent.Gender , Talent.DOB , Talent.Hair , Talent.Eye , Talent.Height , Talent.Clothessize , Talent.shoesize , Talent.hobbies , Talent.BIO , Talent.Compcard , Talent.ID FROM Talent where Talent.DOB between ('{ts '1996-03-19 22:06:56'}') and ('{ts '2000-03-19 22:06:56'}') </PRE>" <P>Data Source = "INTERACT" <P> <P>The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (16:1) to (16:48) in the template file C:\Documents and Settings\Greg Stone.GREG\My Documents\client coldfusion workfiles\interact\application\members\test.cfm.</P></FONT> --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/<BR> <BR> <P><B><FONT SIZE=2 FACE="Arial">IMPORTANT NOTICE:</FONT></B></P> <P><B><FONT SIZE=2 FACE="Arial">This e-mail and any attachment to it is intended only to be read or used by the named addressee. It is confidential and may contain legally privileged information. No confidentiality or privilege is waived or lost by any mistaken transmission to you. If you receive this e-mail in error, please immediately delete it from your system and notify the sender. You must not disclose, copy or use any part of this e-mail if you are not the intended recipient. The RTA is not responsible for any unauthorised alterations to this e-mail or attachment to it. </FONT></B></P> --- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/</td></tr></table> </div> <div class="msgButtons margintopdouble"> <ul class="overflow"> <li class="msgButtonItems"><a class="button buttonleft " accesskey="p" href="msg02559.html">Previous message</a></li> <li class="msgButtonItems textaligncenter"><a class="button" accesskey="c" href="index.html#02560">View by thread</a></li> <li class="msgButtonItems textaligncenter"><a class="button" accesskey="i" href="maillist.html#02560">View by date</a></li> <li class="msgButtonItems textalignright"><a class="button buttonright " accesskey="n" href="msg02561.html">Next message</a></li> </ul> </div> <a name="tslice"></a> <div class="tSliceList margintopdouble"> <ul class="icons monospace"> </ul> </div> <div class="overflow msgActions margintopdouble"> <div class="msgReply" > <h2> Reply via email to </h2> <form method="POST" action="/mailto.php"> <input type="hidden" name="subject" value="[cfaussie] RE: New look up date by years old with a 2 year sprea d < 2 > 2"> <input type="hidden" name="msgid" value="LYRIS-3664-41697-2003.03.20-08.18.34--archive#mail-archive.com@lists.daemon.com.au"> <input type="hidden" name="relpath" value="cfaussie@lists.daemon.com.au/msg02560.html"> <input type="submit" value=" WALTERS Scott "> </form> </div> </div> </div> <div class="aside" role="complementary"> <div class="logo"> <a href="/"><img src="/logo.png" width=247 height=88 alt="The Mail Archive"></a> </div> <form class="overflow" action="/search" method="get"> <input type="hidden" name="l" value="cfaussie@lists.daemon.com.au"> <label class="hidden" for="q">Search the site</label> <input class="submittext" type="text" id="q" name="q" placeholder="Search cfaussie"> <input class="submitbutton" name="submit" type="image" src="/submit.png" alt="Submit"> </form> <div class="nav margintop" id="nav" role="navigation"> <ul class="icons font16"> <li class="icons-home"><a href="/">The Mail Archive home</a></li> <li class="icons-list"><a href="/cfaussie@lists.daemon.com.au/">cfaussie - all messages</a></li> <li class="icons-about"><a href="/cfaussie@lists.daemon.com.au/info.html">cfaussie - about the list</a></li> <li class="icons-expand"><a href="/search?l=cfaussie@lists.daemon.com.au&q=subject:%22%5C%5Bcfaussie%5C%5D+RE%5C%3A+New++look+up+date+by+years+old+with+a+2+year+sprea++++d+++%3C++2+++%3E+2%22&o=newest&f=1" title="e" id="e">Expand</a></li> <li class="icons-prev"><a href="msg02559.html" title="p">Previous message</a></li> <li class="icons-next"><a href="msg02561.html" title="n">Next message</a></li> </ul> </div> <div class="listlogo margintopdouble"> </div> <div class="margintopdouble"> </div> </div> </div> <div class="footer" role="contentinfo"> <ul> <li><a href="/">The Mail Archive home</a></li> <li><a href="/faq.html#newlist">Add your mailing list</a></li> <li><a href="/faq.html">FAQ</a></li> <li><a href="/faq.html#support">Support</a></li> <li><a href="/faq.html#privacy">Privacy</a></li> <li class="darkgray">LYRIS-3664-41697-2003.03.20-08.18.34--archive#mail-archive.com@lists.daemon.com.au</li> </ul> </div> </body> </html>