Hi Chris, Thanks for the info!
....only I still get 0 records. It seems it's trying to search a specific time now: where timereceived between {ts '2006-12-19 11:26:17'} and {ts '2006-12-20 11:26:17'} Anyway to ignore/remove the time? -----Original Message----- From: Christopher Jordan [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 20, 2006 11:16 AM To: CF-Talk Subject: Re: time/date format in Access Hi Robert, I think the problem is that your dates are not in ODBC date format. Try something like this: <cfset Today = now()> <!--- should automatically be in ODBC date format ---> <cfset Yesterday = DateAdd("d",-1,Today)> <!--- Notice it's 'd' not 'dd' ---> <cfquery name="get" datasource="barracuda_log"> SELECT * FROM message_log_20391 WHERE timereceived BETWEEN #Yesterday# AND #Today# </cfquery> I think this will work for you. Cheers, Chris Orlini, Robert wrote: > I have a SQL statement that searches a date/time field in MS Access which has > data such as "12/20/2006 10:02:18 AM". I want to display dates between the > current day and one day behind. > > This statement keeps giving me 0 records. > > <cfset month = #dateformat(now(),"mm")#> > <cfset year = #dateformat(now(),"yyyy")#> > <cfset year2 = #dateformat(now(),"yyyy")#> > <cfset month2 = #dateformat(now(),"mm")#> > <cfset day = #dateformat(now(),"dd")#> > <cfset day2 = DateFormat(Now(),"dd")-1> > > <CFSET today = Createdate(Year,Month,day)> > <CFSET onedaybehind = Createdate(Year2,Month2,day2)> > > <CFQUERY name="get" datasource="barracuda_log"> > SELECT * FROM message_log_20391 > where timereceived between #onedaybehind# and #today# > > I also tried this suggestion: > <CFSET today = Createdatetime(Year,Month,day, 23, 59, 59)> > <cfset onedaybehind = createDateTime(year2, month2, day2, 0, 0, 0) /> > > But still 0 records. > > Any suggestions would be appreciated. > > Thanks! > > Robert O. > HWW > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Create robust enterprise, web RIAs. Upgrade & integrate Adobe Coldfusion MX7 with Flex 2 http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:264602 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4