SELECT DATE,COUNT(DATE) 1

[jcroft]

strSQLcount = "SELECT DATE,count(date) FROM POCHANGE WHERE CHGTYP = '2' AND PONUM='" & ponum1 & "'"

produces this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'PONUM' as part of an aggregate function.

I have read and tried every suggestion that is posted regarding "count" and cannot get anything to work...I am sure something small is stopping me, but.........

 

[jcroft]

sorry, the specified expression 'PONUM" ahould read 'DATE'

 

[lobstah]

strSQLcount = "SELECT DATE,count(date) FROM POCHANGE WHERE CHGTYP = '2' AND PONUM='" & ponum1 & "' GROUP BY DATE"

 

[jcroft]

now I get this error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

I tried:

strSQLcount = "SELECT DATE,count('date') AS cnt FROM POCHANGE WHERE CHGTYP = '2' AND PONUM='" & ponum1 & "'GROUP BY DATE"

strSQLcount = "SELECT DATE,count(date) AS cnt FROM POCHANGE WHERE CHGTYP = '2' AND PONUM='" & ponum1 & "'GROUP BY DATE"

I also added field to select then get failed to use field in aggregate function.

I changed punctuation all kinds of ways, to no avail...

 

[lobstah]

& "'GROUP BY DATE"

you need a space between the ' and the word GROUP, else it runs in with your ponum1 value.

& "' GROUP BY DATE"

 

[lobstah]

a good way to troubleshoot these errors is to do a

response.write strSQLcount

before your recordset.open statement, then when you get the error, you can see exactly what your sql statement contained.

 

[jcroft]

Thank you lobstah for your time and help...and for being so gracious.