RE: (OT) Access SQL Statement
Comparing dates is a pain in the *%% as I'm sure you've found. Try formatting the dates as ODBCDateTimeFormat() inside the SQL statement. If you can't set the date with that function, the database isn't going to read it correctly either. ---mark = Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = -Original Message- From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]] Sent: Friday, July 20, 2001 12:05 PM To: CF-Talk Subject: Re: (OT) Access SQL Statement A dump of the PriSched table reveals that STime and ETime look like such: 1899-12-30 06:00:00 1899-12-30 07:00:00 1899-12-30 07:00:00 1899-12-30 11:00:00 So, what does the following query not work? SELECT STime, ETime, Name, Describe FROM PriSched WHERE ((NOT STime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00) OR (ETime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00)) AND ((NOT STime BETWEEN 1899-12-30 21:00:00 AND 1899-12-30 00:00:00) OR (ETime BETWEEN 1899-12-30 21:00:00 AND 1899-12-30 00:00:00)) ORDER BY STime, ETime" It throws the following error. I've matched the datatypes in the SQL. [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((NOT STime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00) OR (ETime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00)) AND ((NOT STime BETWEEN 1899-12-30 21:00'. Thanks, Dave - Original Message - From: "Mark Warrick" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 12:38 PM Subject: RE: (OT) Access SQL Statement > > > = > Mark Warrick - Fusioneers.com > Personal Email: [EMAIL PROTECTED] > Business Email: [EMAIL PROTECTED] > Phone: 714-547-5386 > Efax: 801-730-7289 > Personal URL: http://www.warrick.net > Business URL: http://www.fusioneers.com > ICQ: 125160 / AIM: markwarric > = > With Access and SQL, you put the NOT before an otherwise true condition. > > WHERE NOT StartTime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 > 18:30:00 > > ---mark > > > -Original Message- > From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]] > Sent: Thursday, July 19, 2001 9:52 AM > To: CF-Talk > Subject: (OT) Access SQL Statement > > > I'm used to Oracle, so I always choke on Access stuff . . . > How do you do a NOT BETWEEN in Access? > (Note: StartTime is a Date/Time Field) > > EX: >SELECT StartTime >FROM myTalbe >WHERE StartTime NOT BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 > 18:30:00 >ORDER BY StartTime > > Thanks, > Dave > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: (OT) Access SQL Statement
A dump of the PriSched table reveals that STime and ETime look like such: 1899-12-30 06:00:00 1899-12-30 07:00:00 1899-12-30 07:00:00 1899-12-30 11:00:00 So, what does the following query not work? SELECT STime, ETime, Name, Describe FROM PriSched WHERE ((NOT STime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00) OR (ETime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00)) AND ((NOT STime BETWEEN 1899-12-30 21:00:00 AND 1899-12-30 00:00:00) OR (ETime BETWEEN 1899-12-30 21:00:00 AND 1899-12-30 00:00:00)) ORDER BY STime, ETime" It throws the following error. I've matched the datatypes in the SQL. [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '((NOT STime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00) OR (ETime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00)) AND ((NOT STime BETWEEN 1899-12-30 21:00'. Thanks, Dave - Original Message - From: "Mark Warrick" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Friday, July 20, 2001 12:38 PM Subject: RE: (OT) Access SQL Statement > > > = > Mark Warrick - Fusioneers.com > Personal Email: [EMAIL PROTECTED] > Business Email: [EMAIL PROTECTED] > Phone: 714-547-5386 > Efax: 801-730-7289 > Personal URL: http://www.warrick.net > Business URL: http://www.fusioneers.com > ICQ: 125160 / AIM: markwarric > = > With Access and SQL, you put the NOT before an otherwise true condition. > > WHERE NOT StartTime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 > 18:30:00 > > ---mark > > > -Original Message- > From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]] > Sent: Thursday, July 19, 2001 9:52 AM > To: CF-Talk > Subject: (OT) Access SQL Statement > > > I'm used to Oracle, so I always choke on Access stuff . . . > How do you do a NOT BETWEEN in Access? > (Note: StartTime is a Date/Time Field) > > EX: >SELECT StartTime >FROM myTalbe >WHERE StartTime NOT BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 > 18:30:00 >ORDER BY StartTime > > Thanks, > Dave > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: (OT) Access SQL Statement
= Mark Warrick - Fusioneers.com Personal Email: [EMAIL PROTECTED] Business Email: [EMAIL PROTECTED] Phone: 714-547-5386 Efax: 801-730-7289 Personal URL: http://www.warrick.net Business URL: http://www.fusioneers.com ICQ: 125160 / AIM: markwarric = With Access and SQL, you put the NOT before an otherwise true condition. WHERE NOT StartTime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00 ---mark -Original Message- From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 19, 2001 9:52 AM To: CF-Talk Subject: (OT) Access SQL Statement I'm used to Oracle, so I always choke on Access stuff . . . How do you do a NOT BETWEEN in Access? (Note: StartTime is a Date/Time Field) EX: SELECT StartTime FROM myTalbe WHERE StartTime NOT BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00 ORDER BY StartTime Thanks, Dave ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: (OT) Access SQL Statement
Access uses the pound sign to delimit dates: SELECT StartTime FROM myTalbe WHERE StartTime NOT BETWEEN #1899-12-30 18:00:00# AND #1899-12-30 18:30:00# ORDER BY StartTime You'll probably have to double them up to use the query in CF. tom "Skyward Internet Technology" <[EMAIL PROTECTED]> wrote in message 024601c11073$1f9645f0$bd08eb84@jasper">news:024601c11073$1f9645f0$bd08eb84@jasper... > I'm used to Oracle, so I always choke on Access stuff . . . > How do you do a NOT BETWEEN in Access? > (Note: StartTime is a Date/Time Field) > > EX: >SELECT StartTime >FROM myTalbe >WHERE StartTime NOT BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 > 18:30:00 >ORDER BY StartTime > > Thanks, > Dave > > > ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
