VB 6 ado open using VFP 6 sql select with group by 1

[ashmac2]

I have the following code that is giving me an invalid Group by clause error:

Set adoPrimaryRS = New Recordset

->  adoPrimaryRS.Open "SELECT userid, max(saidtime) AS saidtime, actualtime, punchtype, description, returntime FROM punches WHERE saidtime > DATE() GROUP BY userid", db, adOpenStatic, adLockOptimistic

The sql select statement works fine in VFP and only errors out here. Any ideas? Thanks in advance.

Laura Savana

 

[ChewDoggie]

I believe your group by clause has to reference "actualtime, punchtime, description and returntime" as well, but not aggregate functions such as "max".

HTH

Many Thanks!

AMACycle

American Motorcyclist Association

http://www.ama-cycle.org

 

[ashmac2]

Thanks, that got me thinking in the right direction and we managed to get our results in the following way:

SELECT userid,saidtime,actualtime,punchtype, ;
              descriptio,returntime ;
	FROM c:\matt\data\punches ;
	WHERE saidtime >= DATE() AND ;
              userid+TTOC(saidtime) IN ;
	(SELECT userid+TTOC(MAX(saidtime)) ;
		FROM c:\matt\data\punches ;
		WHERE saidtime >= DATE() ;
		GROUP BY userid)