To SUM a table by month in VB6
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.
- 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