CFQUERY error Anyone see an error!

[ryanstokes1234]

I have a problem,this query will not do what I need it to. It keeps giving me
an error, as shown below. I have tried everything I know to fix it... and
nothing has fixed it.

The table contains information
emailaddress,fname,lname,streetaddress,city,state, zip,hphone,wphone,mphone as
well as a Primary Automated number called "ParentCode"

Anyone have any ideas on how to fix this?

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Syntax error (missing operator) in query expression
'Ucase(emailaddress) = Ucase(Bob@a.com)'.

The error occurred in
D:\www\a\l\allsaintsnurseryschool.com\www\registra tion3.cfm: line 104

102 : <cfquery datasource="allsaints" name="email">
103 : SELECT DISTINCT emailaddress FROM registeredparents
104 : WHERE Ucase(emailaddress) = Ucase(#SESSION.emailaddress#)
105 : </cfquery>

[Knum]

102 : <cfquery datasource='allsaints' name='email'> 103 : SELECT DISTINCT
emailaddress FROM registeredparents 104 : WHERE Ucase(emailaddress) =
Ucase(#SESSION.emailaddress#) 105 : </cfquery> More your ucase pound to the
outside and put some ' ' around it may help alittle

[zoeski80]

Aren't UCase and LCase the CF functions and UPPER and LOWER the SQL Functions?

WHERE UPPER(emailaddress) = '#UCase(SESSION.emailaddress)#'

HTH
Zoe

[paross1]

Since his error message indicates an Access database, UCase and LCase are valid
functions for both ColdFusion and Access, so either of these will work. Don't
forget the single quotes around the text parameter!

<cfquery datasource="allsaints" name="email">
SELECT DISTINCT emailaddress
FROM registeredparents
WHERE Ucase(emailaddress) = Ucase('#SESSION.emailaddress#')
</cfquery>

<cfquery datasource="allsaints" name="email">
SELECT DISTINCT emailaddress
FROM registeredparents
WHERE Ucase(emailaddress) = '#Ucase(SESSION.emailaddress)#'
</cfquery>

Phil

[philh]

That said, Phil, shouldn't we just use the ANSI functions to make the query portable? I'd hate to upsize an application that relied on Access-specific functions in all the queries ;^)

[paross1]

Sometimes that happens when migrating from one db to another. I know that going
from Access to SQL Server is not too bad, but going from Oracle to SQL Server
or vice versa can be a major pain. I agree that using "standard" function names
would definitely be preferred, but sometimes it isn't possible. I usually like
to give multiple examples when there is a choice, especially so that they can
recognize that there are differences that may trip them up later.

Phil