|
Greg
The
error is in your FROM clause
Try
This
<cfset form.olderthan =5>
<cfset form.youngerthan =1>
<cfset form.search = "yes">
<cfset form.gender = "male">
<cfset form.hair = "">
<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#
<cfelse>
<cfif parameterexists(form.search)>
WHERE 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>
</cfif>
</cfquery>
Hi steve removed the WHERE 1=1
but know luck..
should i change the
AND dob BETWEEN
DateAdd(Year,#Form.OlderThan#,dob) AND
DateAdd(Year,#Form.YoungerThan#,dob)
move the hashes around the dob as
well?
[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.
SQL = "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 AND
Talent.gender = 'male' AND dob BETWEEN DateAdd(Year,5,dob) AND
DateAdd(Year,1,dob)"
----- Original Message -----
Sent: Wednesday, March 19, 2003 2:54 PM
Subject: [cfaussie] Re: look up date by years old with a 2 year
spread < 2 > 2
Check your columns names
If
its not that, re jig the code to get rid of the WHERE 1=1 part. I know
this works under MSSQL, but not sure if it works under
Access.
What
is does it just returns true cause 1 does = 1, and then you just add your AND
clauses. Dont know if it works in Access
Anyone?
Steve
I have added the error
Thanks sarah steve and adam
the cfif was the source of the last error now i
have a new one.
it looks like the dob which i thought would be
evaluated from the database selected record is not the case.
do i need to pass the dob vale from the form or
from a query on the page.
I would like to use the between younger older
compared to the value in the datase.
At this time i just pass a select vale for
OlderThan and YoungerThan and now dob vale is sent to the
query.
Is it possible to just send the 2 values and
call the dob from the database?
and what format should the dob be
in?
should it be Talent.DOB?
<cfset Form.OlderThan =5>
<cfset
Form.YoungerThan =1>
<cfset form.search = "yes">
<cfset
form.gender = "male">
<cfset form.hair = "">
<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>
The error
ODBC Error Code = 07001 (Wrong number of parameters)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.
Hint: The cause of this error is usually that your query contains
a reference to a field which does not exist. You should verify that the
fields included in your query exist and that you have specified their names
correctly.
SQL = "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 AND Talent.gender = 'male' AND dob BETWEEN
DateAdd(Year,5,dob) AND DateAdd(Year,1,dob)"
Data Source = "INTERACT" ---
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/
|
