Skip to main content
December 4, 2012
Question

Coldfusion Query With Function Help

  • December 4, 2012
  • 2 replies
  • 759 views

Hello!  I am using Coldfusion 9 as well as the current version of MySQL.  My current query is throwing an error:

<CFQUERY NAME="getmemgal" DATASOURCE="#ds#">

SELECT usrid, usrmem, usrlogin, usrastro, memid, memlast, memfirst, memend, memlife

FROM usr, mem

WHERE usrmem LIKE memid

AND usrastro LIKE 1

AND ( memlife LIKE 1 OR CreateODBCDate(CreateDate(memend+1,1,15)) > CreateODBCDate(Now()) )

ORDER BY memlast, memfirst

</CFQUERY>

Right now, the database stores the most recent year for which dues are paid as an integer, in "memend".  This query gives the members a 15-day grace period before their member photo gallery stops showing up on the site.  This means that a member who last paid for 2012 will have until January 15, 2013 to pay their dues.  Hence this part of the query:

AND ( memlife LIKE 1 OR CreateODBCDate(CreateDate(memend+1,1,15)) > CreateODBCDate(Now()) )

So you can see that their membership expires on January 15 of the year following the end of the payment.  But, this query is throwing a syntax error.  Can anybody help me correct this?

-Scott

This topic has been closed for replies.

2 replies

Inspiring
December 5, 2012

1. May be in this NOw(), try within # signs.

AND ( memlife LIKE 1 OR CreateODBCDate(CreateDate(memend+1,1,15)) > CreateODBCDate(#Now()#) )

2.

AND ( memlife LIKE 1 OR (CreateODBCDate(CreateDate(memend+1,1,15)) > CreateODBCDate(#Now()#)) )

Inspiring
December 5, 2012

Actually, he needs octothorps araund every variable. 

WolfShade
Legend
December 4, 2012

Specifics, please.  Error message, line number, etc.

^_^

December 4, 2012

Error Executing Database Query.

FUNCTION cva_ver_01.CreateODBCDate does not exist

The error occurred in R:\_Web\cva\gallery.cfm: line 1

Called from R:\_Web\cva\Application.cfc: line 21

Called from R:\_Web\cva\gallery.cfm: line 1

Called from R:\_Web\cva\Application.cfc: line 21