Guys... what's wrong on this code ???
I'm getting this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
/marcos/t4w/savehours.asp, line 22"
The line 22 is:
set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
can anyone help me??
Thanks !!
Comments
:
: I'm getting this error:
:
: "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
: /marcos/t4w/savehours.asp, line 22"
:
:
: The line 22 is:
:
: set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
:
:
: can anyone help me??
:
: Thanks !!
:
I believe you have to format date and time strings special, like surrounding them with special characters. For example, some DB's require a format of #03:30:30#, others '03:30:30', and so on. Check the documentation for your DB's SQL
----------------
Walt
: Guys... what's wrong on this code ???
:
: I'm getting this error:
:
: "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
: /marcos/t4w/savehours.asp, line 22"
:
:
: The line 22 is:
:
: set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
:
:
: can anyone help me??
:
: Thanks !!
:
[code]
set rsCheck = connection.execute("SELECT * FROM hours WHERE starttime BETWEEN #"& newstarttime &"# AND #"& newendtime &"# OR endtime BETWEEN #"& newstarttime &"# AND #"& newendtime &"#"
[/code]
If your DB is not access,use single quotes instead of # around the date values.
HTH
Faustine
--------
: : Guys... what's wrong on this code ???
: :
: : I'm getting this error:
: :
: : "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
: : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
: : /marcos/t4w/savehours.asp, line 22"
: :
: :
: : The line 22 is:
: :
: : set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
: :
: :
: : can anyone help me??
: :
: : Thanks !!
: :
:
: [code]
: set rsCheck = connection.execute("SELECT * FROM hours WHERE starttime BETWEEN #"& newstarttime &"# AND #"& newendtime &"# OR endtime BETWEEN #"& newstarttime &"# AND #"& newendtime &"#"
: [/code]
: If your DB is not access,use single quotes instead of # around the date values.
:
:
: HTH
:
: Faustine
: --------
:
But my DB is MS-Access.... will it work using "#" ??
If not ,is there another solution ?
thanks !
: :
: : I'm getting this error:
: :
: : "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
: : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
: : /marcos/t4w/savehours.asp, line 22"
: :
: :
: : The line 22 is:
: :
: : set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
: :
: :
: : can anyone help me??
: :
: : Thanks !!
: :
: I believe you have to format date and time strings special, like surrounding them with special characters. For example, some DB's require a format of #03:30:30#, others '03:30:30', and so on. Check the documentation for your DB's SQL
:
:
: ----------------
: Walt
:
:
I use Ms-access... so , in this case, maybe it's better to get the value on each field (in this case starttime and endtime), and use it as string ?
i had formatted "newstarttime" and "newendtime" variables to Date/Hour and tried to compare them to the fields "starttime" and "Endtime" of my DB, where theses fields are the type Date/Hour
: : : Guys... what's wrong on this code ???
: : :
: : : I'm getting this error:
: : :
: : : "Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
: : : [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '03:30:00 BETWEEN starttime AND endtime OR 05:00:00 BETWEEN starttime AND endtime'.
: : : /marcos/t4w/savehours.asp, line 22"
: : :
: : :
: : : The line 22 is:
: : :
: : : set rsCheck = connection.execute("SELECT * FROM hours WHERE " & newstarttime & " BETWEEN starttime AND endtime OR " & newendtime & " BETWEEN starttime AND endtime")
: : :
: : :
: : : can anyone help me??
: : :
: : : Thanks !!
: : :
: :
: : [code]
: : set rsCheck = connection.execute("SELECT * FROM hours WHERE starttime BETWEEN #"& newstarttime &"# AND #"& newendtime &"# OR endtime BETWEEN #"& newstarttime &"# AND #"& newendtime &"#"
: : [/code]
: : If your DB is not access,use single quotes instead of # around the date values.
: :
: :
: : HTH
: :
: : Faustine
: : --------
: :
:
: But my DB is MS-Access.... will it work using "#" ??
:
: If not ,is there another solution ?
:
:
: thanks !
:
:
It should work with # ,but the problem seems that there is no date value in that field .Try adding a date to that field.
newstarttime = "01/01/2003 "& newstarttime
newendtime = "01/01/2003 "& newendtime
and then call the query :
set rsCheck = connection.execute("SELECT * FROM hours WHERE starttime BETWEEN #"& newstarttime &"# AND #"& newendtime &"# OR endtime BETWEEN #"& newstarttime &"# AND #"& newendtime &"#"
I am not sure if this will work,but you could try it.
Faustine
---------