# Visual Basic 6 help



## Scully (Oct 1, 2000)

I'm working on a program that stores data input to a SQL database. I need to now create some reports from the data stored. What is the best way to do this? Here's what I have and need to do:

Data:

Table1:
customerid
customername

Table2:
customerid
saleamount
saledate

The report needs to look like this:

```
customerid     customername        daterange        percentageoftotal
                                   totalamount    %of total


                                    TOTAL

ex:
                               07-13-01
                                 thru
                               07-20-01
--------------------------------------------------
0001     some customer         23,987.53      35%
0002     another customer      45,329.83      65%

--------------------------------------------------
                       Total:  69,317.36     100%
```
Any help would be greatly apprciated, as well as thoughts or ideas.

I would like the user running the report to be able to select the date ranges for as many ranges as they want up to 6 different ranges. So the would get a form prompt that would ask them to enter the date ranges and then the report would run.

Cheers,
Scully

[Edited by Scully on 07-25-2001 at 08:17 PM]


----------



## YSB (Mar 7, 1999)

Sorry it took so long to get here. I've been very busy last week and barely got online at all, let alone TSG.

First, VB6 should come with a report tool. Unfortunately I have never looked at it (or even looked _for_ it) so I couldn't be of much help in that. It is supposed to exist though and if try you might be able to get it to work for you.

Without the report tool though you can still extract and display the data you want. I am assuming you want the data displayed in a form? Or do you want it on paper? In either event you would probably want to base this on two SQL statements.

The first query would just be a total of all sales in the given period used for comparison to determine the percentage. You would filter the query by date the same way you filter the second query which will be discussed later. For best results, put the total of sales in a variable that we'll call intTotalSales

The second would look something like this:


```
Select customerid, customername, Sum(saleamount) As SumSale, 100 / intTotal * SumSale As Percent
Where Table1.customerid = table2.customerid
```
I'll discuss options for the date filter later but it would go here.

```
Group by customername, customerid
```
Now I'm not sure I follow where you want to go with the multiple date ranges. Do you want one report to handle several ranges or do you want the report to run several times each with another range?

Regardless, the best way to do the filter is to create the SQL statement in code based on some input. For example, you could create a form with boxes for six ranges and test which ones are blank and which ones are used. It would probably be easiest to make a control array so the code could be more easily recycled. For one range at a time the code for creating the SQL statement would look something like this:


```
Dim StrSql as String
Dim X as Integer

For X = 1 to NumberOfReportsToGenerate

  StrSql = "Select customerid, customername, Sum(saleamount) As SumSale, 100 / " & _
  intTotal  & " * SumSale As Percent" & _
  "  Where Table1.customerid = table2.customerid" & _
  "  And saledate > " & txtBeginDate(X) & _
  "  And saledate < " & txtEndDate(X) & _
  "  Group BY customername, customerid"

  'Put code to display report here. I'll put more details later 

Next X
```
If you want to use several date ranges in one report you would need to use the loop while creating the query.


```
Dim StrSql as String
Dim X as Integer

StrSql = "Select customerid, customername, Sum(saleamount) As SumSale, 100 / " & _
intTotal & " * SumSale As Percent" & _
"  Where Table1.customerid = table2.customerid AND ("

For X = 1 to NumberOfRanges

  If X <> 1 then StrSql = StrSql & " OR "
  StrSql = StrSql & " (saledate > " & txtBeginDate(X) & _
  " And saledate < " & txtEndDate(X) & ") "

Next X

StrSql = StrSql & "  Group BY customername, customerid"
```
As far as displaying the report goes, one option would be to use a regular form. In this case you would open a recordset based on the above SQL statement and attach it to a data consumer such as a datagrid. On top you would put the ranges that where originally entered and on bottom you would put the contents of the first total query.

Another option would be to export the data to a text file which you can then print. You would first print the ranges to the file, then you would loop through the recordset and do the same followed by the total.

One more option would be to automate Ms Word to create the report. You would create a template and poke in the data.

I hope this helps. Let us know what happens. Good Luck! 

[Edited by YSB on 07-29-2001 at 04:27 PM]


----------



## Scully (Oct 1, 2000)

YSB, Thanks so much. This put's me on the right track...One question for you. I'm tring to work through the code you gave me and I can't seem to get even the first Query to work. I'm trying to verify the query by having it output the intTotalSales to the debug window. How ever it's not working for me. Here's my code:


```
Private Sub cmdView_Click()
  Dim c As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  c.Open "DSN=custsales"
  Set rs = c.Execute("SELECT Sum(SaleAmount) As TotalSales, FROM Table2")
  rs.MoveFirst
  While Not rs.EOF
    Debug.Print rs!TotalSales
    rs.MoveNext
  Wend
End Sub
```
What am I missing here? I'm sorry to admit I've been working on this for hours trying to verify the output!AAARRRGGH! Any way thanks for any help.


----------



## Scully (Oct 1, 2000)

Got the SQL query worked out!....

Now moving on to creating the report/output...

How would I go about the word doc thing?

Cheers,
Scully


----------



## Scully (Oct 1, 2000)

I can't seem to get the query to show as currency or the percentage. Here's what I've got in the query:


```
("SELECT SUM(SaleAmount) As SumOfSales FROM TSales WHERE TSales.SaleDate " & _
  " BETWEEN #" & txtR1Date1 & "# " & _
  " And #" & txtR1Date2 & "# ")
```
Thanks


----------

