Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
asm
Posting Yak Master
140 Posts |
 Posted - 2005-11-09 : 05:54:56
|
| HiI am generating a accounts ledger (using cr6)1. generate view (GenLed)2. from vb6 - create two tables Ogenled & Tgenled from query genledOgenled - Data before from date enter by userTgenled - Data between from date - To date enter by user3. Update tgenled table from ogenled table (summary of amount) as opening in tgenled (if code found in tgenled else addnew in tgenled)visual basic code:---------------------------------------------------Private Sub CmdPreview_Click() CmdPreview.Enabled = False Dim strRepName As String Dim SqlString As String Set cmdobj = New Command Set ComObjUpd = New Command Set RoGenLed = New ADODB.Recordset Set RtGenLed = New ADODB.Recordset Dim opamt As Double Dim opac As String ' Create Temporary Table - Preview Report With cmdobj .ActiveConnection = ConnectAcct .CommandText = "SELECT * INTO OGENLED FROM GENLED WHERE DOCDATE <#" & TxtFromDate.Text & "# OR (OAMOUNT <> 0 AND DOCDATE IS NULL) ORDER BY GLNAME" .Execute .CommandText = "SELECT * INTO TGENLED FROM GENLED WHERE DOCDATE >=#" & TxtFromDate.Text & "# AND DOCDATE<=#" & TxtToDate.Text & "# ORDER BY GLNAME" .Execute End With' Update Opening Amount from Ogenled table to TGENLED table With cmdobj .ActiveConnection = ConnectAcct .CommandText = "select glname, oamount, sum(tamount) as tamt from ogenled group by glname, oamount order by glname" End With Set RoGenLed = cmdobj.Execute RtGenLed.Open "TGENLED", ConnectAcct, adOpenKeyset, adLockPessimistic, adCmdTable If Not ((RoGenLed.BOF = True) And (RoGenLed.EOF = True)) Then RoGenLed.MoveFirst Do While Not RoGenLed.EOF opamt = 0 opac = RoGenLed!glname If IsNull(RoGenLed!tamt) Then opamt = RoGenLed!oamount Else opamt = RoGenLed!oamount + RoGenLed!tamt End If If opamt <> 0 Then If Not RtGenLed.EOF And Not RtGenLed.BOF Then RtGenLed.MoveFirst End If Do While Not RtGenLed.EOF If UCase(opac) = UCase(RtGenLed!glname) Then RtGenLed!oamount = opamt RtGenLed.Update GenFound = True End If RtGenLed.MoveNext Loop 'Not found then add new If GenFound = False Then RtGenLed.AddNew RtGenLed!glname = opac RtGenLed!oamount = opamt RtGenLed.Update End If End If RoGenLed.MoveNext Loop End If RtGenLed.Requery RoGenLed.Close RtGenLed.Close' Report Preview in Crystal Report strRepName = "Ledger.rpt" SqlString = "SELECT * FROM TGENLED ORDER BY GLNAME, DOCDATE" Me.CrystalReport1.ReportFileName = strRepName Me.CrystalReport1.SQLQuery = SqlString Me.CrystalReport1.Destination = crptToWindow Me.CrystalReport1.Connect = "dsn= " & DsnName & "" Me.CrystalReport1.Formulas(0) = "compname = '" & CompanyName & "'" Me.CrystalReport1.Formulas(1) = "rfdate = '" & TxtFromDate.Text & "'" Me.CrystalReport1.Formulas(2) = "rtdate = '" & TxtToDate.Text & "'" Me.CrystalReport1.Action = 1' Drop Temporary Table - Preview Report With cmdobj .ActiveConnection = ConnectAcct .CommandText = "DROP TABLE OGENLED" .Execute .CommandText = "DROP TABLE TGENLED" .Execute End With CmdPreview.Enabled = True cmdobj.ActiveConnection = Nothing Set RoGenLed = Nothing Set RtGenLed = NothingEnd Sub------------------------------------------------Can it possible with query or single table. pls guide because this will take too much time.thanksasm |
|
|
|
|
|
query / table