p2p.wrox.com: Archives - asp

asp_databases thread: Re: How to sum the return from a recordset be tween specified dates..ATTN: Shane

Message #1 by <nickm@s...> on Wed, 31 Jan 2001 15:54:19 -0500

Hi:



Changing the last WHERE to AND made it work. IS there some rule where you

can't use more than one WHERE statement in an SQL string?



The problem now it is returning hours worked from last year (23/01/00) even

though the query supposed to include only hours worked in the last 7 days?



Any ideas?



Thanks,



Stefan





----- Original Message -----

From: "Blake, Shane" <Shane.Blake@p...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, January 31, 2001 3:33 PM

Subject: [asp_databases] Re: How to sum the return from a recordset be tween

specified dates..





> 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 #2 by "Blake, Shane" <Shane.Blake@p...> on Wed, 31 Jan 2001 15:58:23 -0500

you can use as many where clauses as you can keep up with...



you just can't put the keyword WHERE in more than once... seperate the rest

of your where clauses with the logical keywords AND and/or OR as dictated by

the logic required.





shane.blake

it.consultant

www.metrois.com

raligh.nc







-----Original Message-----

From: nickm@s... [mailto:nickm@s...]

Sent: Wednesday, January 31, 2001 3:54 PM

To: ASP Databases

Subject: [asp_databases] Re: How to sum the return from a recordset be

tween specified dates..ATTN: Shane





Hi:



Changing the last WHERE to AND made it work. IS there some rule where you

can't use more than one WHERE statement in an SQL string?



The problem now it is returning hours worked from last year (23/01/00) even

though the query supposed to include only hours worked in the last 7 days?



Any ideas?



Thanks,



Stefan





----- Original Message -----

From: "Blake, Shane" <Shane.Blake@p...>

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, January 31, 2001 3:33 PM

Subject: [asp_databases] Re: How to sum the return from a recordset be tween

specified dates..





> 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 #3 by "Wally Burfine" <oopconsultant@h...> on Wed, 31 Jan 2001 21:20:07 -0000

Yes you can only have one WHERE





>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 be    

>tween specified dates..ATTN: Shane

>Date: Wed, 31 Jan 2001 15:54:19 -0500

>

>Hi:

>

>Changing the last WHERE to AND made it work. IS there some rule where you

>can't use more than one WHERE statement in an SQL string?

>

>The problem now it is returning hours worked from last year (23/01/00) even

>though the query supposed to include only hours worked in the last 7 days?

>

>Any ideas?

>

>Thanks,

>

>Stefan

>

>

>----- Original Message -----

>From: "Blake, Shane" <Shane.Blake@p...>

>To: "ASP Databases" <asp_databases@p...>

>Sent: Wednesday, January 31, 2001 3:33 PM

>Subject: [asp_databases] Re: How to sum the return from a recordset be 

>tween

>specified dates..

>

>

> > 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

> > > >

>

Return to Index

P2P Archives