sql openrecordset error

[kkson]

I am trying to get the code below to order by nsn but when i add the code i get an error: 'excute a query that does not include the specified expression 'NSN' as part of an aggregate function.'

Set rsHR = Cdb.OpenRecordset("Select Count([HRID])as HR from qryrptflighthr WHERE PID = '" & Me!PID "' ORDER BY NSN")

If i add NSN by the SELECT it don't help either.

Thanks,

 

[Remou]

This may help:
Query : aggregate Function Error
thread701-1220873

 

[kkson]

Changed the code to:

Set rsHR = Cdb.OpenRecordset("Select NSN, ITEM, UI, ISSUEDQTY, PID, Count([HRID])as HR FROM qryrptflighthr GROUP BY NSN, ITEM, UI, ISSUEDQTY, WHERE PID = '" & Me!PID & "'")

now i get a syntax error (missing operator) in qry expression 'where pid = rb3355

 

[Remou]

You seem to have an extra comma:

ISSUEDQTY[red],[/red] WHERE PID = '" & Me!PID & "'")

 

[kkson]

removed the comma, now the error is: syntax error (missing operator) in qry expression 'issuedqty where pid = rb3355"

thanks,

 

[Remou]

I think Group By needs to be after Where.

 

[kkson]

okay moved it and no errors. However now it only shows 1 record. before it showed all 59 records but with no order.

 

[Remou]

I am somewhat puzzled, if grouping by NSN returns one record, it seems to imply that all NSNs are the same, so how would you sort by that field? Perhaps you could post some sample data?

 

[kkson]

figured it out. I just put the sort order i wanted on the ORDER BY field on the reports data properties. Haven't had go about it that way before, could always set the report source or vba to select order.

thanks