To SUM a table by month in VB6

[kevrich69]

I will have two text boxes 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 month table name is month
The sales table is housesales

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 for two tables in two separate command buttons.
one for table housesales, and the other for table bar_sales.

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.

 
Dim MyCon As New ADODB.Connection
 Dim housesales As New ADODB.Recordset
 Dim sConnect As String
 Dim food_sales
 food_sales = Format("#########.00")
 
 
 text2.Text = Format$("#########.00") 'Format$(formula2, "#,###,000.00")
 
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=" & _
 App.Path & "\CHEF_HELPER1.MDB"
 
MyCon.Open sConnect
 housesales.Open "select sum(food_sales) from housesales", MyCon, adOpenDynamic, adLockPessimistic
 text2.Text = housesales.Fields(0)
 housesales.Close
 MyCon.Close
 

It sums all the data in table bar_sales successfully.

Thank you.
Kevin

[kevrich69]

I made a mistake in the description, I will just be using one table, housesales, and two fields... food_sales, and bar_sales.

[Killer42]

It sounds as though you just need to add a WHERE clause in your SQL, to limit it to records matching the month.

However, what I don't understand is this code...

Dim food_sales
food_sales = Format("#########.00")
text2.Text = Format$("#########.00")
 

As far as I can tell, all you're doing there is setting an otherwise-unused variable (food_sales, of type variant) and a textbox, to the value "#########.00". The Format function is used to take a numeric value and convert it to some display format. Since you haven't supplied a value, presumably it's taken to be zero.

(Format and Format$ are the same thing, by the way. The latter is simply for backward compatibility with very old code.)

[kevrich69]

Thank you for your reply.
I have removed the code you mentioned because it was left in when I was trying some different code which didn't work....

But I'm not sure how the correct code should be using the where clause. I have tried it before and get an error, probably because I'm leaving something out.

Could you give me an example of using the where clause with month? Would it be something like this?

On Error Resume Next
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 * FROM housesales WHERE month Like '" & TextBox3.Text & "%'"
 
 housesales.Open "select sum(food_sales) from housesales", MyCon, adOpenDynamic, adLockPessimistic
Text4.Text = Format$(housesales.Fields(0), "#########00.00")
 housesales.Close
 MyCon.Close
 

This isn't working. I type in July 2012 in text3.text and it sums June 2012 and July 2012 in text4.text

[Killer42]

Sorry for the delay, I don't get to the site very often these days. Other forums here are much more active, of course; the pool of people who remember much about these 12-years-and-older VB versions probably isn't that great these days.

To provide a definite answer to this I'd need to know a bit more about your file. Most importantly, how is the month stored in the sales table? I mean, the field type and what values do you hold there? (Keep in mind I'm not expert with Access and SQL, I've just dabbled a bit over the years - the real experts hang out over in the Access/VBA forum).

If it's a Text field with values like "July 2012" stored there, then you can probably just change "Like" to "=", drop the "%" and it'll work.

For the sake of argument, I'll assume it's a Date/Time type, with the actual date (and possibly the time of day) that each sale was made. A query something like this might work:

housesales.Open "SELECT * FROM housesales WHERE month between #1 Jul 2012# AND #31 Jul 2012#"

Obviously this would require your code to generate the start and end values for the range. I've just hard-coded the start and end of July here.


Or alternatively:

housesales.Open "SELECT * FROM housesales WHERE Format(month,'mmmm yyyy') = 'July 2012'"

This one might allow you to use your value from TextBox3 directly.



Note, there's a fair chance that the first sample above would actually miss sales on 31st July. It's fiddly working with date/time fields, because they store time as well as date. If you're storing an actual time of day, the end of the range would need to be something like #31 Jul 2012 23:59:59#.