ActiveReport w/ADO (by code) connected to SQL Server

[Ausburgh]

I hope I can explain the issue I'm having suffiently ... in any case here it is:

I connected to the SQL server successfully but I'm having problems generating my reports anyway. And I suspect it's due to the & "" below ... but if I remove them I get an error too.

Field1.DataValue = !SumOfASal & ""
Field2.DataValue = !SumOfAAwd & ""
Field3.DataValue = !SumOfABen & ""
Field4.DataValue = !SumOfAOT & ""

Here's an example of my problem:
--------------------------------
Problem is:

If
SumOfASal = 13088.86
SumOfAAwd = 4925
SumOfABen = 1000.92
SumOfAOT = 526.96

and

Field5.DataValue = Field1.DataValue + Field2.DataValue + Field3.DataValue + Field4.DataValue

the result being generated becomes: 13088.8649251000.92526.96 instead of 19541.74

which is creating a Type mismatch.
------------------------------------------
How can I fix this?

Thanks in advance.

 

[gmmastros]

Could is be as simple as:

Field5.DataValue = [!]val([/!]Field1.DataValue[!])[/!] + [!]val([/!]Field2.DataValue[!])[/!] + [!]val([/!]Field3.DataValue[!])[/!] + [!]val([/!]Field4.DataValue[!])[/!]


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ausburgh]

Thanks George that was it.

Be patience with me ... this VB novice is learning.

Thanks again.

 

[Ausburgh]

Thanks George that was it.

Be patient with me ... this VB novice is learning.

Thanks again.

 

[gmmastros]

Val converts a string to a number. Without using the val statements, you were effectively appending strings together. By converting the values to a number, then adding them, you got the sum.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ausburgh]

I have multiple records grouped by Branch which rolls all records in the "View table" up into 5 records(again grouped by Branch).

When I used the DAO control I was able to generate one report with all 5 branches (on separate pages) with:

Private Sub ghBranch_Format()
    ghBranch.NewPage = ddNPBefore
End Sub

but now that I'm using ADODB connection it's generating the report with ONLY the first record in the "View table". Where am I going wrong?

 

[gmmastros]

What database are you using?
What does the query look like?


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ausburgh]

I'm trying to extract the info from the SQL server. The query on the SQL side is kind of huge so here's a small piece of it:

SELECT 
dbo.ALLOCATED.*, dbo.OBLandEXPD.*, 
dbo.ALLOCATED.SumOfASal * .4344 AS SE_ASal,
dbo.ALLOCATED.SumOfASal * .5656 AS RE_ASal,
dbo.ALLOCATED.SumOfAAwd * .4344 AS SE_AAwd,
dbo.ALLOCATED.SumOfAAwd * .5656 AS RE_AAwd
FROM
dbo.ALLOCATED INNER JOIN
dbo.OBLandEXPD ON dbo.ALLOCATED.ABranch = dbo.OBLandEXPD.EBranch

and on the VB side - I connected with (again a small piece):

Private Sub ActiveReport_ReportStart()
    Set Conn = New ADODB.Connection
        
    'define the recordset access statement
    Dim strSQL As String
    strSQL = "SELECT * FROM ALLOCATEDandEXPENSED ORDER BY ABranch"
    Set rs = New ADODB.Recordset
            
    Conn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dbFSF;Data Source=ABC-00001"
    Conn.Open
    
    With rs
        .ActiveConnection = Conn
        .CursorLocation = adUseServer
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = strSQL
        .Open
    End With
    
    With rs
        Field127.DataValue = !ABranch & ""
        Field1.DataValue = !SumOfASal & ""
        Field16.DataValue = !SumOfAAwd & ""
        Field5.DataValue = !SumOfABen & ""
        Field12.DataValue = !SumOfAOT & ""
        Field23.DataValue = !SumOfATrav & ""
    End With    
End Sub

Private Sub ghBranch_Format()
    ghBranch.NewPage = ddNPBefore
End Sub

 

[gmmastros]

I'm sorry. I suppose I mis-understood the question. This appears to be an 'active-report' issue. Since I am unfamiliar with active report, I don't think I'll be much help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom