Access with ColdFusion

[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.

 

[Melagan]

What about:

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

Is the date field in your table formatted as date/time or text?


~Melagan
______
"It's never too late to become what you might have been.

 

[micjohnson]

Thank you Melagan!

Query with "WHERE DateValue(dtReceipt) = #01/01/2006#" is working well. But while i am using

WHERE DATEPART("yyyy", dtReceipt) = "2006"
AND DATEPART("m", dtReceipt) = "01" on another query to show the report, it is not working.

Regards
micJ

 

[dhookom]

Don't compare numbers to strings/text.

WHERE DATEPART("yyyy", dtReceipt) = 2006
AND DATEPART("m", dtReceipt) = 1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[micjohnson]

Thanks a lot dhookom and Melagan. Queries are working now. (Sorry i am new to MS Access) Thanks for your time.

Regards
micJ