This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C0C366.68AE779E
Content-Type: text/plain;
	charset="iso-8859-1"

Ok, here's one for the guru's. I've developed a transportation system for a
newspaper a while ago, i used vb with a sql back end and created a slu of
generic dynamic data reports (without horrible data environment, sorry i
really hate the extra dependancy, not to mention it makes for stupid people
to think they are actually programmers. AND, it's buggy). The users can
select any field they want to group on, any field they want to sort on, and
any date range on date fields. It then uses the ADO shape command to create
heirarchies for groupings based on the field selections. Here is a taste.
 
Here are some test field values:
dtpStartAccDate = '01/01/00'
dtpEndAccDate = '04/01/01'
cboAccSortColumn.Tag = 'name'
cboAccSortOrder.Listindex = 0 'Ascending
 
"SHAPE {SELECT employees.first_name + ' ' + employees.last_name as name," &
_
"accident.accident_num, accident.license_num , accident.vehicle_num,
accident.police_report_num, accident.insurance_case_num,
accident.insurance_company," & _
"datepart(mm,accident.accident_date) mth, accident.accident_date, case_open
= case case_open_flag when 1 then 'Open' when 0 then 'Closed' else 'Unknown'
end " & _
FROM employees INNER JOIN accident on employees.employee_id 
accident.employee_id " & _
"WHERE datediff(day, '" & dtpStartAccDate.Value & "',
accident.accident_date) > 0 and datediff(day,'" & dtpEndAccDate.Value & "',
accident.accident_date) < 0 order by " & cboAccSortColumn.Tag & _
 IIf(cboAccSortOrder.ListIndex = 0, " ASC", " DESC") & "} AS Command1
COMPUTE Command1 BY 'mth'")
 
So my problem is this. Within the data report, its grouping by month via
datepart and the compute clause. Which gives me date numbers (1-12). If i
put a dataformat(field property) on, it always returns january. If i do it
in the query by changing datepart(mm,accident.accident_date) month TO
datename(month, datepart(mm,accident.accident_date) mth). It returns
something different nearly every time, sometimes january is february,
sometimes march is september. Anyone know a workaround?