Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2012

    Unanswered: To SUM a table by month in VB6


    I'm a newbie working in vb6 and would like some help with some code from an expert...

    I am working on a restaurant program where the daily sales are saved into a table. There is a column for the month,(January 2012) 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 2012 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 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 2912 is in text2.text, it will sum all the sales in housesales where January 2012 is the month. The same for the rest of the months. (I might put the months into a combo box)

    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.

    Private Sub Command2_Click()
    Dim MyCon As New ADODB.Connection
    Dim housesales As New ADODB.Recordset
    Dim sConnect As String

    sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & _
    App.Path & "\CHEF_HELPER1.MDB"

    MyCon.Open sConnect
    housesales.Open "select sum(bar_sales) from housesales", MyCon, adOpenDynamic, adLockPessimistic
    Text1.Text = housesales.Fields(0)

    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.

  2. #2
    Join Date
    Mar 2004
    Make sure you post in the correct forum. You have posted in MySQL forum yet you are using the JET database which is a microsoft product and not the same database at all. ask a moderator to move the thread to the correct forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts