Please can anyone help with this Syntax

"SELECT Count(RECEIVED) AS CountOfRECEIVED, OFFICER, Count(LDD > Expiry)
AS OOT, (OOT/CountOfRECEIVED) AS num_ber, (num_ber*100) AS per_cent FROM
Application GROUP BY OFFICER;"
 
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
2.

The query works in Access 2002 but not in ASP

Code in Access is

SELECT Count(Application.RECEIVED) AS CountOfRECEIVED,
Application.OFFICER, Count([LDD]>[Expiry]) AS OOT,
([OOT]/[CountOfRECEIVED]) AS num_ber, ([num_ber]*100) AS per_cent
FROM Application
GROUP BY Application.OFFICER;

If used like for like does not work in Asp

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
 

For some reason, Microsoft decided that, through available ADO providers, 
Access SQL syntax would be like SQL Server. If you play around a bit, like 
with wildcard specifications, you'll see what I mean.

So, you may want to adjust your SQL statement accordingly. A few things to 
keep in mind:

- Get in the habit of enclosing object names (i.e. tables, fields, stored 
procedures, views, indexes, etc.) in braces ([]) to avoid conflict with 
keywords

- Avoid Access-specific commands and syntax

Your problem appears to be in the "COUNT(LDD>Expiry)" part.

And be prepared: some of your Access Query objects may not function 
properly either, unless their syntax is common to BOTH Access AND SQL 
Server. Visit the Microsoft site for more info.

Skip

Thanks for your help
The problem is with
"COUNT(LDD>Expiry)"

I have tried every permutation I can think of but with no luck

The idea is to compare two dates and get a count of where one exceeds
another


-----Original Message-----
From: skip@f... [mailto:skip@f...] 
Sent: Sunday, March 09, 2003 5:44 PM
To: ASP Databases
Subject: [asp_databases] Re: SQL Syntax

For some reason, Microsoft decided that, through available ADO
providers, 
Access SQL syntax would be like SQL Server. If you play around a bit,
like 
with wildcard specifications, you'll see what I mean.

So, you may want to adjust your SQL statement accordingly. A few things
to 
keep in mind:

- Get in the habit of enclosing object names (i.e. tables, fields,
stored 
procedures, views, indexes, etc.) in braces ([]) to avoid conflict with 
keywords

- Avoid Access-specific commands and syntax

Your problem appears to be in the "COUNT(LDD>Expiry)" part.

And be prepared: some of your Access Query objects may not function 
properly either, unless their syntax is common to BOTH Access AND SQL 
Server. Visit the Microsoft site for more info.

Skip

---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
 

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
 

Have you looked into using the DateDiff function?
http://www.devguru.com/Technologies/vbscript/quickref/datediff.html

Regards,
Peter Foti


> -----Original Message-----
> From: donohue ian [mailto:ianmdonohue@c...]
> Sent: Monday, March 10, 2003 5:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: SQL Syntax
> 
> 
> 
> 
> Thanks for your help
> The problem is with
> "COUNT(LDD>Expiry)"
> 
> I have tried every permutation I can think of but with no luck
> 
> The idea is to compare two dates and get a count of where one exceeds
> another
> 
> 
> -----Original Message-----
> From: skip@f... [mailto:skip@f...] 
> Sent: Sunday, March 09, 2003 5:44 PM
> To: ASP Databases
> Subject: [asp_databases] Re: SQL Syntax
> 
> For some reason, Microsoft decided that, through available ADO
> providers, 
> Access SQL syntax would be like SQL Server. If you play around a bit,
> like 
> with wildcard specifications, you'll see what I mean.
> 
> So, you may want to adjust your SQL statement accordingly. A 
> few things
> to 
> keep in mind:
> 
> - Get in the habit of enclosing object names (i.e. tables, fields,
> stored 
> procedures, views, indexes, etc.) in braces ([]) to avoid 
> conflict with 
> keywords
> 
> - Avoid Access-specific commands and syntax
> 
> Your problem appears to be in the "COUNT(LDD>Expiry)" part.
> 
> And be prepared: some of your Access Query objects may not function 
> properly either, unless their syntax is common to BOTH Access AND SQL 
> Server. Visit the Microsoft site for more info.
> 
> Skip
> 
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
>  
> 
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
>  
> 
> 
> 

would something like : SELECT COUNT(*) FROM Table WHERE LDD>Expiry do 
the trick?

-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Tuesday, 11 March 2003 9:50 AM
To: ASP Databases
Subject: [asp_databases] Re: SQL Syntax


Have you looked into using the DateDiff function?
http://www.devguru.com/Technologies/vbscript/quickref/datediff.html

Regards,
Peter Foti


> -----Original Message-----
> From: donohue ian [mailto:ianmdonohue@c...]
> Sent: Monday, March 10, 2003 5:07 PM
> To: ASP Databases
> Subject: [asp_databases] Re: SQL Syntax
>
>
>
>
> Thanks for your help
> The problem is with
> "COUNT(LDD>Expiry)"
>
> I have tried every permutation I can think of but with no luck
>
> The idea is to compare two dates and get a count of where one exceeds
> another
>
>
> -----Original Message-----
> From: skip@f... [mailto:skip@f...]
> Sent: Sunday, March 09, 2003 5:44 PM
> To: ASP Databases
> Subject: [asp_databases] Re: SQL Syntax
>
> For some reason, Microsoft decided that, through available ADO
> providers,
> Access SQL syntax would be like SQL Server. If you play around a bit,
> like
> with wildcard specifications, you'll see what I mean.
>
> So, you may want to adjust your SQL statement accordingly. A
> few things
> to
> keep in mind:
>
> - Get in the habit of enclosing object names (i.e. tables, fields,
> stored
> procedures, views, indexes, etc.) in braces ([]) to avoid
> conflict with
> keywords
>
> - Avoid Access-specific commands and syntax
>
> Your problem appears to be in the "COUNT(LDD>Expiry)" part.
>
> And be prepared: some of your Access Query objects may not function
> properly either, unless their syntax is common to BOTH Access AND SQL
> Server. Visit the Microsoft site for more info.
>
> Skip
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
> 
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
> 
>
>
>

How about:

SELECT
    COUNT(a.Received) AS CountOfReceived,
    a.Officer,
    (
        SELECT
            COUNT(*)
        FROM
            Application AS b
        WHERE
            b.LLD > b.Expiry
        AND
            b.Office = a.Officer
    ) AS Oot,
FROM
    Application AS a
GROUP BY
    a.Officer

and the rest you can calculate in your frontend application (you could
execute more subqueries, but performance probably wouldn't be very good)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
----- Original Message -----
From: "donohue ian" <ianmdonohue@c...>
To: "ASP Databases" <asp_databases@p...>
Sent: Monday, March 10, 2003 2:22 AM
Subject: [asp_databases] SQL Syntax


:
: Please can anyone help with this Syntax
:
: "SELECT Count(RECEIVED) AS CountOfRECEIVED, OFFICER, Count(LDD > Expiry)
: AS OOT, (OOT/CountOfRECEIVED) AS num_ber, (num_ber*100) AS per_cent FROM
: Application GROUP BY OFFICER;"
:
: Error Type:
: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected
: 2.
:
: The query works in Access 2002 but not in ASP
:
: Code in Access is
:
: SELECT Count(Application.RECEIVED) AS CountOfRECEIVED,
: Application.OFFICER, Count([LDD]>[Expiry]) AS OOT,
: ([OOT]/[CountOfRECEIVED]) AS num_ber, ([num_ber]*100) AS per_cent
: FROM Application
: GROUP BY Application.OFFICER;
:
: If used like for like does not work in Asp
:
: ---
: Outgoing mail is certified Virus Free.
: Checked by AVG anti-virus system (http://www.grisoft.com).
: Version: 6.0.449 / Virus Database: 251 - Release Date: 1/27/2003
:
:
: