Sum of hours using select statement & VBA

Sum of hours using select statement & VBA

• Ri-Karou

  Hi Rhys,

  My appologies to duck thing for jumping in here... your SELECT statement is a tad wrong, I think your wanting an alias.

  Change the SELECT statement to use AS instead of INTO...

  SELECT SUM(activityhours) AS [sumactivityhours] FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'"

  also I'd put the code in the companyname combo box AfterUpdate event (if it's driven from a combo box) rather than the Exit event.

  The format statement takes a number and formats it into a string based on a format expression. The "#,##0.00" is a format expression... if you have to display currency then you can use "￡##0.00" and the number is displayed as ￡5.90... look into the help for more information here as there is a lot to it.

  
  
  

• SYED HANIF SH

  Hi duck thing - ive gotten this far, which im hoping is correct up until now but im now unsure as to how to take the value and place it into the appropriate field - 'sumactivityhours'

  Private Sub companyname_Exit(cancel As Integer)
  Dim rsdbase As Database
  Dim rstemp As Recordset
  Set rsdbase = CurrentDb
  Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")

  End Sub

  Thanks,

  Rhys.

  
  

• Subrajit

  Hi duck thing - i entered as you said so my statement is now:

  Private Sub companyname_Exit(cancel As Integer)
  Dim rsdbase As Database
  Dim rstemp As Recordset
  Set rsdbase = CurrentDb
  Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")
  With rstemp
  .MoveFirst
  Me.sumactivityhours = Format(CDbl(!activityhours), "#,##0.00")

  .Close
  End With

  End Sub

  but get the following message:

  Run time error '3265':

  Item not found in this collection

  and it poins to the line: Me.sumactivityhours = Format(CDbl(!activityhours), "#,##0.00")

  Any ideas

  Thanks,

  Rhys.

  
  

• jmcdonaldtucson

  Thanks duck thing - i can see what youre saying - ill give it a go and see how far i get.

  
  

• anubisascends

  Hi Derek and duck thing - that now works great except for one issue - if no hours have been logged against a company and you try to log an activity for the first time it gives the message 'Error 94: invalid use of null' and points to the Me.sumactivityhours = Format(CDbl(!sumactivityhours), "#,##0.00") - which i assume is because there has been no prior hours entered that can be summed up - where in the statement would i enter an IF statement to overcome this and would it be something like this

  IF Me.sumacticityhours = "" Then

  Me.sumactivityhours = 0

  Thanks a lot guys,

  Rhys.

  
  

• payal tandon

  The SELECT statement doesn't return a number -- it returns a table with one record which contains the SUM you've asked for. So I don't believe you can directly set sumactivityhours.Value to the query string. I believe you'll need to open a recordset using your SELECT query, grab the value from the sum field, then plug that value into the appropriate field.

  I apologize for not being able to provide specific code; I don't have access to Access at the moment!

  
  
  

• hrubesh

  ok - i get the message:

  Run Time Error '2113':

  The value you entered isnt valid for this field

  The code i have is below:

  Private Sub companyname_Exit(cancel As Integer)
  Me.sumactivityhours.Value = "SELECT SUM(activityhours)FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'"
  End Sub
  

  where 'sumactivityhours' is the name of the field im trying to populate with the select statement - the error message tells me to debug the line of code - i take it there is a mistake in the Me.sumactivityhours.value part of the statement.

  Any ideas

  Thanks,

  Rhys.

  
  

• ShrikantBijapurkar

  Thanks duck thing and derek, that works perfectly - you guys are the best!

  Rhys.

  
  

• yln

  You've got it right. Your query will probably look something like

  "SELECT SUM(activityhours) FROM [tblactivity] WHERE [companyname] = '" & Cstr(Me.ComboBox1.Value) & "'"

  
  
  

• nfuji27

  Sure. What's happening is that the new recordset you're viewing with your SELECT statement doesn't have a field called "activityhours". I'm not sure exactly what the field is called once it's summed up. What you can do is change your SELECT statement to include "SELECT SUM(etc...) INTO [SumActivityHours] (etc...)". Then change CDbl(!activityhours) to CDbl(!SumActivityHours) and you should be set.

  
  
  

• brockmoore

  Hi duck thing, i'm getting no joy with this at all! I changed the syntax as you said (se end of thread) but it doesnt like and says theres an invalid operatin on the SELECT line - im assuming its the into part - i didnt realise that it was possible to use INTO in a select statement. If i take the INTO statement out i then get the previous error 'item not found in this collection' and it points to the me.sumactivityhours =format line. I dont know what to do!

  For clarity purposes i should explain the scenario a little better: the field 'activityhours' is the field the user enters the number of hours per activity againts a particular company, of which there could be many entries and therefore many hours. 'sumactivityhours' is the field on the same form (and same table) that I want to populate with the sum total of 'activityhours' values when a user selects the appropriate company. Im not very experienced with VBA as you can tell so i dont really know how to go and fix the problem - i understand that movefirst selects the first row in the recordset , and that should be the only row but im unsure as to what the format line does, and what the exclamation mark does

  Any ideas

  Many thanks for your time,

  Rhys.

  Private Sub companyname_Exit(cancel As Integer)
  Dim rsdbase As Database
  Dim rstemp As Recordset
  Set rsdbase = CurrentDb
  Set rstemp = rsdbase.OpenRecordset("SELECT SUM(activityhours)INTO [sumactivityhours] FROM [tblactivity] WHERE [companyname] ='" & CStr(Me.companyname.Value) & "'")
  With rstemp
  .MoveFirst
  Me.sumactivityhours = Format(CDbl(!sumactivityhours), "#,##0.00")
  .Close
  End With

  
  

• StevenR2

  There is a nice obscure function in Access you can use to handle null conditions like the one your getting. It's called NZ()...

  It's like an short if null statement... here's how you'd use it

  Me.sumactivityhours = Format(Nz(!sumactivityhours, 0), "#,##0.00")

  If !sumactivityhours is null then use 0 otherwise use the value of !sumactivityhours.

  
  
  

• Beej

  Something like this should do it (inserted before your "End Sub") :

  With rstemp
  .MoveFirst
  me.sumactivityhours = Format(CDbl(!activityhours),"#,##0.00") ' or whatever format you prefer
  .Close
  End With

  Does that fly

  
  
  
• Sum of hours using select statement & VBA