Ahhh ... I have just looked down the
thread ... and seen Steve's comments here re: the #'s.
-----Original
Message-----
From: Steve Onnis
[mailto:[EMAIL PROTECTED]
Sent: Wednesday, 19 March 2003
12:41 AM
To: CFAussie Mailing List
Subject: [cfaussie] Re: look up
date by years old with a 2 year spread < 2 > 2
you would
better off passing in a value for the time span, as in oler and/or younger
values
So the
form would submit, "I want all people older than 18 and younger that
30"
the
query would look like this
<CFQUERY
name="getRecord" dataSource="interact">
SELECT Talent.talentID ,
Talent.Name ,
Talent.Gender ,
Talent.DOB ,
Talent.Hair ,
Talent.Eye ,
Talent.Height ,
Talent.Clothssize ,
Talent.shoesize ,
Talent.hobbies ,
Talent.BIO ,
Talent.Compcard ,
Talent.ID
FROM Talent
WHERE 1=1
<CFIF ParameterExists(url.ID)>AND Talent.ID = #url.ID#</CFIF>
<CFIF ParameterExists(form.search)>
AND Talent.gender = '#form.gender#'
<!--- Check for age --->
<!--- If we want an age range --->
<CFIF Form.OlderThan AND Form.YoungerThan>
AND dob BETWEEN DateAdd(Year,#Form.OlderThan#,dob) AND
DateAdd(Year,#Form.YoungerThan#,dob)
<cfif>
<!--- If we want all people younger than an age --->
<cfif Form.YoungerThan AND NOT Form.OlderThan>
AND dob < DateAdd(Year,#Form.YoungerThan#,dob)
</cfif>
<!--- If we want all people older than an age --->
<cfif NOT Form.YoungerThan AND Form.OlderThan>
AND dob > DateAdd(Year,#Form.OlderThan#,dob)
</cfif>
<CFIF form.hair IS NOT "">and talent.hair =
'#form.hair#'</CFIF>
</CFIF>
</CFQUERY>
SQL has
its own DateAdd() function, so dont put and hashes(#) around them in the
query. this way you are making the comparison directly against the DOB
column
Regards
Steve Onnis
Domain Concept Designs
+61 422 337 685
+61 3 9431 4249
("If you think it can't be done, you haven't
asked me!") - Steve Onnis
-----Original Message-----
From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of [EMAIL PROTECTED]
Sent: Tuesday, 18 March 2003 9:24
PM
To: CFAussie Mailing List
Subject: [cfaussie] Re: look up
date by years old with a 2 year spread < 2 > 2
Query by years old on dob field
search by years old with
a 2 year spread each side of there date of birth .
eg: 5 years old Query =
select any member between 3 years and 7
The data base is set-up with DOB field
eg 31/12/1997 Query = select
any user between 31/12/1995 and 31/12/1999
I cant work out the less than 2
years and more than 2 years with the date field
i would like to send a number to the
query but it could be a year or????
Hope all this is clear and thanks in
advance if anyone can help on my short comings with date issues and queries.
<CFQUERY
name="getRecord" dataSource="interact">
SELECT Talent.talentID , Talent.Name , Talent.Gender , Talent.DOB ,
Talent.Hair , Talent.Eye , Talent.Height , Talent.Clothssize , Talent.shoesize
, Talent.hobbies , Talent.BIO , Talent.Compcard , Talent.ID
FROM Talent
<CFIF ParameterExists(url.ID)>
WHERE Talent.ID = #url.ID#
</CFIF>
<CFIF ParameterExists(form.search)>
WHERE Talent.gender = '#form.gender#'
<CFIF
form.dob IS NOT "">
and talent.dob is #form.dob# ( could be a number or a year )
</CFIF>
<CFIF form.hair IS NOT
"">
and talent.hair = '#form.hair#'
</CFIF>
</CFIF>
</CFQUERY>
---
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/
---
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/
---
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/
---
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/
______________________________________________________________________
This email, including attachments, is intended only for the addressee
and may be confidential, privileged and subject to copyright. If you
have received this email in error, please advise the sender and delete
it. If you are not the intended recipient of this email, you must not
use, copy or disclose its content to anyone. You must not copy or
communicate to others content that is confidential or subject to
copyright, unless you have the consent of the content owner.
|
|
