ColdFusion with MS Access

[micjohnson]

I am using MS Access as my database for my small ColdFusion application that has 6 or 7 pages overall. My question is when i fetching recrods from database based on the date. Query is working sometimes and not sometimes.

SELECT *
FROM tblHomeBudget
WHERE DateValue(dtReceipt) = '01/01/2006'

I have several rows on my database but query returns 0 records.

I tried with Format(dtReceipt, "MM/DD/YYYY") also. This thing giving me an error.


Same thing for DATEPART("YYYY", dtReceipt) = '2006'
and DATEPART("M", dtReceipt) = '01'

Please advice me.

Thanks in advance.

 

[ecobb]

Try this:

SELECT *
FROM   tblHomeBudget
WHERE  dtReceipt = <cfqueryparam value="01/01/2006" cfsqltype="CF_SQL_DATE">

(For the record, you really shouldn't use "Select *")


Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

 

[SpotsyRWH]

Using CFquery param is always a good idea as it provides better performance, and helps protect your data types, and your database from SQL injection attacks.

 

[r937]

open up access, go to Tables, and open up your table in Design View

what is the datatype of dtReceipt?

r937.com | rudy.ca

 

[micjohnson]

Thank you all for your time. The datatype for the field dtReceipt is Date/Time. The query is working now.

The corrected format is as below.

For DateValue(dtReceipt) = #01/01/2006# (No single quotes. Instead, i added # around)

For DATEPART("YYYY", dtReceipt) = 2006 ( No single quotes)

Thanks again

Regards
micJ

 

[olchik]

Hello, micjohnson
I'd like to ask you about question you posted a while ago.
It's about thread232-1085638. I have posted similar question and so far didn't get any replies. If you found the solution for that problem, could you please reply for my thread? Please help if you can. Thanks a lot. My thread was posted on Jan 9, 06.