|
 |
asp_databases thread: How to sum the return from a recordset between specified dates..
Message #1 by <nickm@s...> on Wed, 31 Jan 2001 13:17:12 -0500
|
|
Hi:
I want to be able to sum the employee_time within 7 days of the date of the
query. In other words; I want to be able to return how many hours a given
employee has worked in the last 7 days. I used this SQL statement and it
gave me the error right underneath:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
AND THE ERROR:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID
'Santo''.
/studiowebback/reports.asp, line 33
Thanks a lot.
Stefan
Message #2 by "Greg Covey" <gecovey@s...> on Wed, 31 Jan 2001 11:36:12 -0800
|
|
Are you using an Access DB? SQL Server? You should use # around dates for
access, and single quotes around dates for SQL server. . . this might be
your problem.
> -----Original Message-----
> From: nickm@s... [mailto:nickm@s...]
> Sent: Wednesday, January 31, 2001 10:17 AM
> To: ASP Databases
> Subject: [asp_databases] How to sum the return from a recordset between
> specified dates..
>
>
> Hi:
>
> I want to be able to sum the employee_time within 7 days of the
> date of the
> query. In other words; I want to be able to return how many hours a given
> employee has worked in the last 7 days. I used this SQL statement and it
> gave me the error right underneath:
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
>
> AND THE ERROR:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> operator) in
> query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
> employeeID
> 'Santo''.
>
> /studiowebback/reports.asp, line 33
>
> Thanks a lot.
>
> Stefan
>
>
Message #3 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 19:56:01 -0000
|
|
do it this way:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
WHERE employeeID= '" & id & "';"
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] How to sum the return from a recordset between
>specified dates..
>Date: Wed, 31 Jan 2001 13:17:12 -0500
>
>Hi:
>
>I want to be able to sum the employee_time within 7 days of the date of the
>query. In other words; I want to be able to return how many hours a given
>employee has worked in the last 7 days. I used this SQL statement and it
>gave me the error right underneath:
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
>DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
>
>AND THE ERROR:
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>in
>query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID
>'Santo''.
>
>/studiowebback/reports.asp, line 33
>
>Thanks a lot.
>
>Stefan
>
>
Message #4 by <nickm@s...> on Wed, 31 Jan 2001 14:56:56 -0500
|
|
I am using access.
Thanks
----- Original Message -----
From: "Greg Covey" <gecovey@s...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 2:36 PM
Subject: [asp_databases] RE: How to sum the return from a recordset between
specified dates..
> Are you using an Access DB? SQL Server? You should use # around dates for
> access, and single quotes around dates for SQL server. . . this might be
> your problem.
>
> > -----Original Message-----
> > From: nickm@s... [mailto:nickm@s...]
> > Sent: Wednesday, January 31, 2001 10:17 AM
> > To: ASP Databases
> > Subject: [asp_databases] How to sum the return from a recordset between
> > specified dates..
> >
> >
> > Hi:
> >
> > I want to be able to sum the employee_time within 7 days of the
> > date of the
> > query. In other words; I want to be able to return how many hours a
given
> > employee has worked in the last 7 days. I used this SQL statement and it
> > gave me the error right underneath:
> >
> > strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> > DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
> >
> > AND THE ERROR:
> >
> > Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
> > operator) in
> > query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
> > employeeID
> > 'Santo''.
> >
> > /studiowebback/reports.asp, line 33
> >
> > Thanks a lot.
> >
> > Stefan
> >
>
Message #5 by "Xu Gary" <Gary.Xu@p...> on Wed, 31 Jan 2001 12:09:08 -0800
|
|
Why do you have ";" at the end of the string? Two "where" are wrong too.
Does the following work?
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
WHERE employeeID= " & id
If does, how about this?
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
WHERE employeeID= '" & id & "';"
If does, how about this?
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate = Date()
WHERE employeeID= '" & id & "';"
I believe only the first one works. Before bothering date format, make sure
the basic T-SQL is right.
-----Original Message-----
From: Wally Burfine [mailto:oopconsultant@h...]
Sent: Wednesday, January 31, 2001 11:56 AM
To: ASP Databases
Subject: [asp_databases] Re: How to sum the return from a recordset
between specified dates..
do it this way:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
WHERE employeeID= '" & id & "';"
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] How to sum the return from a recordset between
>specified dates..
>Date: Wed, 31 Jan 2001 13:17:12 -0500
>
>Hi:
>
>I want to be able to sum the employee_time within 7 days of the date of the
>query. In other words; I want to be able to return how many hours a given
>employee has worked in the last 7 days. I used this SQL statement and it
>gave me the error right underneath:
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
>DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
>
>AND THE ERROR:
>
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>in
>query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID
>'Santo''.
>
>/studiowebback/reports.asp, line 33
>
>Thanks a lot.
>
>Stefan
>
>
Message #6 by <nickm@s...> on Wed, 31 Jan 2001 15:15:05 -0500
|
|
Hi:
Thanks for the SQL but I am still getting this error:
Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE DevNoteDate BETWEEN
#31/01/01# AND #24/01/01# WHERE employeeID= 'Santo';
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'DevNoteDate BETWEEN #31/01/01# AND #24/01/01# WHERE
employeeID= 'Santo''.
/reports.asp, line 37
AND this is the suggested SQL:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
WHERE employeeID= '" & id & "';"
Thanks,
Stefan
----- Original Message -----
From: "Wally Burfine" <oopconsultant@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 2:56 PM
Subject: [asp_databases] Re: How to sum the return from a recordset between
specified dates..
> do it this way:
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
> WHERE employeeID= '" & id & "';"
>
>
>
> >From: <nickm@s...>
> >Reply-To: "ASP Databases" <asp_databases@p...>
> >To: "ASP Databases" <asp_databases@p...>
> >Subject: [asp_databases] How to sum the return from a recordset between
> >specified dates..
> >Date: Wed, 31 Jan 2001 13:17:12 -0500
> >
> >Hi:
> >
> >I want to be able to sum the employee_time within 7 days of the date of
the
> >query. In other words; I want to be able to return how many hours a given
> >employee has worked in the last 7 days. I used this SQL statement and it
> >gave me the error right underneath:
> >
> >strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> >DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
> >
> >AND THE ERROR:
> >
> >Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> >in
> >query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
employeeID
> >'Santo''.
> >
> >/studiowebback/reports.asp, line 33
> >
> >Thanks a lot.
> >
> >Stefan
> >
>
Message #7 by "Blake, Shane" <Shane.Blake@p...> on Wed, 31 Jan 2001 15:33:01 -0500
|
|
Select sum(employee_time)AS SUMTIME
FROM DevNotes
WHERE DevNoteDate BETWEEN #31/01/01# AND #24/01/01#
AND employeeID='Santo';
shane.blake
it.consultant
www.metrois.com
raligh.nc
-----Original Message-----
From: nickm@s... [mailto:nickm@s...]
Sent: Wednesday, January 31, 2001 3:15 PM
To: ASP Databases
Subject: [asp_databases] Re: How to sum the return from a recordset
between specified dates..
Hi:
Thanks for the SQL but I am still getting this error:
Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE DevNoteDate BETWEEN
#31/01/01# AND #24/01/01# WHERE employeeID= 'Santo';
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'DevNoteDate BETWEEN #31/01/01# AND #24/01/01# WHERE
employeeID= 'Santo''.
/reports.asp, line 37
AND this is the suggested SQL:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
WHERE employeeID= '" & id & "';"
Thanks,
Stefan
----- Original Message -----
From: "Wally Burfine" <oopconsultant@h...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 2:56 PM
Subject: [asp_databases] Re: How to sum the return from a recordset between
specified dates..
> do it this way:
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
> WHERE employeeID= '" & id & "';"
>
>
>
> >From: <nickm@s...>
> >Reply-To: "ASP Databases" <asp_databases@p...>
> >To: "ASP Databases" <asp_databases@p...>
> >Subject: [asp_databases] How to sum the return from a recordset between
> >specified dates..
> >Date: Wed, 31 Jan 2001 13:17:12 -0500
> >
> >Hi:
> >
> >I want to be able to sum the employee_time within 7 days of the date of
the
> >query. In other words; I want to be able to return how many hours a given
> >employee has worked in the last 7 days. I used this SQL statement and it
> >gave me the error right underneath:
> >
> >strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> >DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
> >
> >AND THE ERROR:
> >
> >Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> >in
> >query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
employeeID
> >'Santo''.
> >
> >/studiowebback/reports.asp, line 33
> >
> >Thanks a lot.
> >
> >Stefan
> >
>
Message #8 by <nickm@s...> on Wed, 31 Jan 2001 15:56:47 -0500
|
|
Hi:
First I like to thank everyone who has helped.
Are you telling me that you cannot use two WHERE statements in an SQL
string?
----- Original Message -----
From: "Xu Gary" <Gary.Xu@p...>
To: "ASP Databases" <asp_databases@p...>
Sent: Wednesday, January 31, 2001 3:09 PM
Subject: [asp_databases] Re: How to sum the return from a recordsetbe tween
specified dates..
> Why do you have ";" at the end of the string? Two "where" are wrong too.
>
> Does the following work?
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
> WHERE employeeID= " & id
>
> If does, how about this?
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
> WHERE employeeID= '" & id & "';"
>
> If does, how about this?
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> DevNoteDate = Date()
> WHERE employeeID= '" & id & "';"
>
> I believe only the first one works. Before bothering date format, make
sure
> the basic T-SQL is right.
>
>
> -----Original Message-----
> From: Wally Burfine [mailto:oopconsultant@h...]
> Sent: Wednesday, January 31, 2001 11:56 AM
> To: ASP Databases
> Subject: [asp_databases] Re: How to sum the return from a recordset
> between specified dates..
>
>
> do it this way:
>
> strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
> WHERE employeeID= '" & id & "';"
>
>
>
> >From: <nickm@s...>
> >Reply-To: "ASP Databases" <asp_databases@p...>
> >To: "ASP Databases" <asp_databases@p...>
> >Subject: [asp_databases] How to sum the return from a recordset between
> >specified dates..
> >Date: Wed, 31 Jan 2001 13:17:12 -0500
> >
> >Hi:
> >
> >I want to be able to sum the employee_time within 7 days of the date of
the
> >query. In other words; I want to be able to return how many hours a given
> >employee has worked in the last 7 days. I used this SQL statement and it
> >gave me the error right underneath:
> >
> >strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> >DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
> >
> >AND THE ERROR:
> >
> >Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> >in
> >query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
employeeID
> >'Santo''.
> >
> >/studiowebback/reports.asp, line 33
> >
> >Thanks a lot.
> >
> >Stefan
> >
>
Message #9 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 21:16:06 -0000
|
|
I put a "WHERE" in the clause and it should have been "AND"
Sorry about that
Wally
>From: <nickm@s...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: How to sum the return from a recordset between
>specified dates..
>Date: Wed, 31 Jan 2001 15:15:05 -0500
>
>Hi:
>
>Thanks for the SQL but I am still getting this error:
>
>Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE DevNoteDate BETWEEN
>#31/01/01# AND #24/01/01# WHERE employeeID= 'Santo';
>Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
>[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
>in
>query expression 'DevNoteDate BETWEEN #31/01/01# AND #24/01/01# WHERE
>employeeID= 'Santo''.
>
>/reports.asp, line 37
>
>AND this is the suggested SQL:
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
>DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
>WHERE employeeID= '" & id & "';"
>
>Thanks,
>
>Stefan
>
>
>----- Original Message -----
>From: "Wally Burfine" <oopconsultant@h...>
>To: "ASP Databases" <asp_databases@p...>
>Sent: Wednesday, January 31, 2001 2:56 PM
>Subject: [asp_databases] Re: How to sum the return from a recordset between
>specified dates..
>
>
> > do it this way:
> >
> > strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> > DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) &
>"#
> > WHERE employeeID= '" & id & "';"
> >
> >
> >
> > >From: <nickm@s...>
> > >Reply-To: "ASP Databases" <asp_databases@p...>
> > >To: "ASP Databases" <asp_databases@p...>
> > >Subject: [asp_databases] How to sum the return from a recordset between
> > >specified dates..
> > >Date: Wed, 31 Jan 2001 13:17:12 -0500
> > >
> > >Hi:
> > >
> > >I want to be able to sum the employee_time within 7 days of the date of
>the
> > >query. In other words; I want to be able to return how many hours a
>given
> > >employee has worked in the last 7 days. I used this SQL statement and
>it
> > >gave me the error right underneath:
> > >
> > >strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> > >DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id &
>"';"
> > >
> > >AND THE ERROR:
> > >
> > >Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> > >
> > >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
>operator)
> > >in
> > >query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
>employeeID
> > >'Santo''.
> > >
> > >/studiowebback/reports.asp, line 33
> > >
> > >Thanks a lot.
> > >
> > >Stefan
> > >
Message #10 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 21:13:26 -0000
|
|
Sure is! Should have been:
strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
(DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#)
AND employeeID= '" & id & "';"
>From: "Xu Gary" <Gary.Xu@p...>
>Reply-To: "ASP Databases" <asp_databases@p...>
>To: "ASP Databases" <asp_databases@p...>
>Subject: [asp_databases] Re: How to sum the return from a recordset be
>tween specified dates..
>Date: Wed, 31 Jan 2001 12:09:08 -0800
>
>Why do you have ";" at the end of the string? Two "where" are wrong too.
>
>Does the following work?
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
>WHERE employeeID= " & id
>
>If does, how about this?
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes
>WHERE employeeID= '" & id & "';"
>
>If does, how about this?
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
>DevNoteDate = Date()
>WHERE employeeID= '" & id & "';"
>
>I believe only the first one works. Before bothering date format, make sure
>the basic T-SQL is right.
>
>
>-----Original Message-----
>From: Wally Burfine [mailto:oopconsultant@h...]
>Sent: Wednesday, January 31, 2001 11:56 AM
>To: ASP Databases
>Subject: [asp_databases] Re: How to sum the return from a recordset
>between specified dates..
>
>
>do it this way:
>
>strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
>DevNoteDate BETWEEN #" & Date() & "# AND #" & DateAdd("d",-7,Date()) & "#
>WHERE employeeID= '" & id & "';"
>
>
>
> >From: <nickm@s...>
> >Reply-To: "ASP Databases" <asp_databases@p...>
> >To: "ASP Databases" <asp_databases@p...>
> >Subject: [asp_databases] How to sum the return from a recordset between
> >specified dates..
> >Date: Wed, 31 Jan 2001 13:17:12 -0500
> >
> >Hi:
> >
> >I want to be able to sum the employee_time within 7 days of the date of
>the
> >query. In other words; I want to be able to return how many hours a given
> >employee has worked in the last 7 days. I used this SQL statement and it
> >gave me the error right underneath:
> >
> >strSQL = "Select sum(employee_time)AS SUMTIME FROM DevNotes WHERE
> >DevNoteDate BETWEEN #Date# AND #Date-7# WHERE employeeID= '" & id & "';"
> >
> >AND THE ERROR:
> >
> >Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> >[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
> >in
> >query expression 'DevNoteDate BETWEEN #Date# AND #Date-7# WHERE
>employeeID
> >'Santo''.
> >
> >/studiowebback/reports.asp, line 33
> >
> >Thanks a lot.
> >
> >Stefan
> >
>
|
|
|
View Cart
