I have recently migrated my database program to Microsoft SQL Server 2005.
I was using access and Microsoft Jet 4.0 to access data, but decided to move to something faster and stronger.
Since that migration the ADO SHAPE command I used for Report grouping no longer works. Here is my code.
SQL$ = "SHAPE(SHAPE(SHAPE" _
& "(SELECT PayrollNum,DeptNum,CheckNumber,FirstN,LastN,HourlyRate, PieceRate, CNARate, CNA," _
& "SSN,CURNet,CURGross,CURRegHrs,CURPcHrs,CURFICA,CURFIT,CURMDCR,BRCUR,SLCUR,CSCUR,OTHCUR," _
& "MTDNet,MTDGross,MTDRegHrs,MTDPcHrs,MTDFICA,MTDFIT,MTDMDCR,BRMTD,SLMTD,CSMTD,OTHMTD," _
& "QTDNet,QTDGross,QTDRegHrs,QTDPcHrs,QTDFICA,QTDFIT,QTDMDCR,BRQTD,SLQTD,CSQTD,OTHQTD," _
& "YTDNet,YTDGross,YTDRegHrs,YTDPcHrs,YTDFICA,YTDFIT,YTDMDCR,BRYTD,SLYTD,CSYTD,OTHYTD FROM " _
& "[REGISTAR" & Combo1.Text & "]" & varProgText & ") AS grp1 " _
& "COMPUTE grp1 BY PayrollNum,DeptNum,SSN,FirstN,LastN,CheckNumber,CNA) AS grp2 " _
& "COMPUTE grp2 BY PayrollNum,DeptNum) AS grp3 " _
& "COMPUTE grp3 BY PayrollNum "
I keep getting this error:
Run-time error '-2147217900(80040e14)':
Data shape command text contains a syntax error at or near position
24 in the command. The text near the error is:
I know i had to make some changes to the query before, like adding brackets between Variables within the SQL statement for Table Names. I am not sure what the error is or modifications I need to make to be able to group this data. It used to work just fine when I was opening the data from an Access Database.
Once the Recordset connects I then assign it as a datasource to a DataReport.