Robin,
What do you mean by, "running on SQL, not Access". MS Access does
contain an implementation of SQL which isn't ANSI standard. However,
the SQL your strSQL variable will contain appears to be both ANSI and
MS Access compliant. Am I missing something?

--

"Robin Hammond" wrote in message ...
Rama,

this is a group by example extracted from one of my routines running on SQL,
not Access.

strSQL = "SELECT Max(ShortName) as ShortName, Max(ItemDescription) as
ItemDescription, Max(ItemIndex) as ItemIndex, Count(ShortName) as ItemCount,
Max(UploadType) as UploadType FROM " & strGetFunctionItems & " WHERE
(Industry IN ("A","B","C") AND IsSeries = " & cBit(bTimeSeries) & ") GROUP
BY ShortName ORDER BY ItemIndex"

So this might return something meaningful...

strSQL = "SELECT Max(TerrCode) as TerrCode, Max(Category_Code) as CatCode,
Max(Salevalue2003) as Sales2003,
Max(Category_Name) as CatName, Max(Territory) as Territory FROM
S12003DataRama WHERE (Date
"&A6&") AND (Date < "&B6&")" GROUP BY TerrCode

You probably don't just want max functions though. You can look at Min,
count, Sum, avg, etc to get what you need.

It's always difficult to get these right without access to the underlying
data, but I hope I've given you a clue. One way to start is to try and
create a query in Access that does what you want for a single parameter and
date, then look at the SQL view of the query.

Robin Hammond
www.enhanceddatasystems.com

"Rama" wrote in message
...
Hi Guys,

The statement below when using SQL.request ( on excel
cells and not VBA) works fine. It returns all the data ,
1) However, now I want to GROUP BY TerrCode and not as now
where it repeats the terrcode each time,I just cannot do
it . Pls help
2) Also the Date returns 37626 , looks award , how can I
give the real date format of 5/01/2003 as type


DSN=MS Access
Database;DBQ=S:\rcamatchee\donthula\FinalHongKongD atabase;D
riverId=281;FIL=MS Access
="SELECT DISTINCT TerrCode,Category_Code, Salevalue2003,
Category_Name, Territory FROM S12003DataRama WHERE (Date
"&A6&") AND (Date < "&B6&")"

5/01/2003 10/01/2003 <<Parameter Type in A6 and B6

=SQL.REQUEST($A$3,,,$A$4)