RE: Access date issue
you may need to put a # around the date if it is a date field in access.. ex.. #dateformat(bdate, m)# = ###Dateformat(searchdate, m)### -- Sam -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:54 AM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Access date issue
At 11:54 AM 2/27/2003 -0800, you wrote: Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Looks like you're using CF function to dynamically create a reference to the SQL column. Assuming: Search Date = 02-01-03 and bdate = 02-14-03, you query will be executed as: - select id,title,sdescrip, bdate from courses where bdate NULL and 2 = 2 -- I imagine you're getting all rows back where bdate isn't null, correct? Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
The error I am getting is : Error resolving parameter BDATE At 12:00 PM 2/27/2003 -0800, you wrote: you may need to put a # around the date if it is a date field in access.. ex.. #dateformat(bdate, m)# = ###Dateformat(searchdate, m)### -- Sam -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:54 AM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
#dateformat(bdate, m)# CF thinks that bdate is a variable. You can't apply a CF function around a column name. You will want to use SQl's built in functions to format your bdate column. There is a better way to do this query. What is the value of bdate and what is the value of searchdate? Bryan F. Hogan Director of Internet Development Macromedia Certified ColdFusion MX Developer Digital Bay Media, Inc. 1-877-72DIGITAL -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 3:02 PM To: CF-Talk Subject: RE: Access date issue The error I am getting is : Error resolving parameter BDATE At 12:00 PM 2/27/2003 -0800, you wrote: you may need to put a # around the date if it is a date field in access.. ex.. #dateformat(bdate, m)# = ###Dateformat(searchdate, m)### -- Sam -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:54 AM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
At 12:01 PM 2/27/2003 -0800, you wrote: The error I am getting is : Error resolving parameter BDATE Make sure bdate is a valid variable in your template! At 12:00 PM 2/27/2003 -0800, you wrote: you may need to put a # around the date if it is a date field in access.. ex.. #dateformat(bdate, m)# = ###Dateformat(searchdate, m)### -- Sam -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:54 AM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
CFQUERY datasource=#application.db# name=howmany select id, title, sdescrip, bdate from courses where not bdate is NULL and month(bdate) = #month(searchdate)# and year(bdate) = #year(searchdate)# order by bdate, title /CFQUERY Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 2:54 PM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
THANKS MATTHEW!!! Joy ;-* ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access date issue
Errr... umm... no, that's the wrong way around ... In your query what you're doing is you're asking MS Access is #CFvariable1# = #cfvariable2# ? which is the same as using literal values like: is 1 = 2? In order to get the result you want, you're going to need to remove the # symbols from the date you want to retreive from the db and use an MS Access function to return the month, so your query should read something like: select id,title,sdescrip, bdate from courses where bdate IS NOT NULL and month(bdate) = #month(searchdate)# order by bdate, title Offhand I don't remember if Access accepts NULL or = NULL but SQL-92 standard db's usually require IS and IS NOT for the evaluation of nulls. hth s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 you may need to put a # around the date if it is a date field in access.. ex.. #dateformat(bdate, m)# = ###Dateformat(searchdate, m)### -- Sam -Original Message- From: Rebecca Joy Sherman [mailto:[EMAIL PROTECTED] Sent: Thursday, February 27, 2003 11:54 AM To: CF-Talk Subject: Access date issue Trying to query a database to get all the records where the bdate is in the same month and year. This example is only the month. But it is not working what am I missing. I am using CF5 and Access CFQUERY datasource=#application.db# name=howmany select id,title,sdescrip, bdate from courses where bdate NULL and #dateformat(bdate, m)# = #Dateformat(searchdate, m)# order by bdate,title /CFQUERY Joy ;-* ~~ ~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index. cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/uns ubscribe.cfm?user=633.558.4 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Get the mailserver that powers this list at http://www.coolfusion.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4