1. Forums
  2. Programming
  3. Visual Basic 6.0
  4. To SUM a table by month in VB6

To SUM a table by month in VB6

Submitted by kevrich69 on
I am working on a restaurant program where the daily sales are saved into a table. There is a column for the month, and a column for food_sales, and bar_sales. I will have two text boxes for food sales, and two for bar sales where one will display the month (January for example) and after clicking on a command button, the second text box will sum the total sales under that month. The database is CHEF_HELPER1.MDB The sales table is housesales The month column name is month The food sales column name is food_sales The bar sales column name is bar_sales I have code that will sum the sales in housesales, but I want to sum it by the month in text1.text. So if January is in text2.text, it will sum all the sales in housesales where January is the month. The same for the rest of the months. I will use the code in two separate command buttons. Code listed below is for bar_sales. I also would like the text1.text formatted as "#########.00" Could some kind soul please help me with the code? What I have now which works is the following.
  1. Private Sub Command2_Click()
  2. Dim MyCon As New ADODB.Connection
  3.  Dim housesales As New ADODB.Recordset
  4.  Dim sConnect As String
  5.  
  6.  
  7. sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  8.  "Data Source=" & _
  9.  App.Path & "\CHEF_HELPER1.MDB"
  10.  
  11. MyCon.Open sConnect
  12.  housesales.Open "select sum(bar_sales) from housesales", MyCon, adOpenDynamic, adLockPessimistic
  13.  Text1.Text = housesales.Fields(0)
  14.  housesales.Close
  15.  MyCon.Close
  16.  
  17. End Sub
It sums all the data in table bar_sales successfully. I just need to have it do it by the month entered in text2.text, and format text1.text as 00.00 Thank you. Kevin