Help! What's wrong with my QUERY statement??

[RuBot]

Also, checking the table, duedate is held as 8/12/2005 whereas if I
#dateformat(duedate,"mm/dd/yyyy")# it will come out with a leading 0. Is that
throwing me off?

Here's my statement


<cfquery datasource="HelpDesk" name = "reminder">
select a.entry_date, a.entry_description, a.emailto, a.remind, b.email
from Help_Desk_List a, TechsTbl b
where ((a.duedate = '#dateformat(now,"mm/dd/yyyy")#') and (a.status =
'Open') and (a.name = b.name) and (a.remind = 'Yes'))
</cfquery>

[RuBot]

<cfset datenow = "#dateformat(now(),"mm-dd-yyyy")#">
<cfquery datasource="HelpDesk" name = "reminder">
select a.entry_date, a.entry_description, a.emailto, a.remind, b.email
from Help_Desk_List a, TechsTbl b
where ((a.duedate = #datenow#)
and (a.status = 'Open') and
(a.name = b.name) and
(a.remind = 'Yes'))
</cfquery>

this also produces no error, but no results as well.

[rmorgan]

I am going to guess its your 'and' statements, you are requireing that all of those exist when they may not therefore, no results. Try changing the 'and' to 'or' and see what you get then.

[CF_Oracle]

What database do you have?
Maybe it's case sencitive then values must be 'OPEN' and "YES". Also most likely #datenow# must be in single quotes:
'#datenow#'.

[san_diego_ca]

Try it without the DateFormat function. The function returns a string and the datatype in the database is most likely a date.

[mxstu]

RuBot,

I don't think Access will interpret the date correctly if you pass it that
way. Try using the CreateODBCDate() method intstead. It ensures that the date
is passed to the database in a format it can interpret correctly. Assuming the
table contains the data that match the WHERE clause conditions, the query below
will return results.

Also, if the "name" field is a "text" field (ex. 'JohnSmith'), you should
think about redesigning the tables. It is a bad idea to use a "text" field as
a foreign key. The reason being that if the "name" field changes in one table,
the other table is not automatically updated. If this happens your data will be
inconsistent and your queries may return incorrect results. A better structure
would be to create a numeric ID in the "TechsTbl" table and reference that
numeric ID in the "Help_Desk_List" table instead of the text "name".


<cfquery datasource="HelpDesk" name = "reminder">
SELECT a.entry_date, a.entry_description, a.emailto, a.remind, b.email
FROM Help_Desk_List a, TechsTbl b
WHERE a.duedate = #CreateODBCDate(now())#
AND a.status = 'Open'
AND a.remind = 'Yes'
AND a.name = b.name
</cfquery>