[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Getting sum from the field

Posted on 2009-02-19
8
Medium Priority
?
170 Views
Last Modified: 2013-11-25
How to get sum from the field with specific range of date and display it in the text box?
My code  below is the code which i use to get the sum of the (IncomeAmount) but i want to get same total or sum with specific range of date.
Thanks...
 
Private Sub Income()
Call OPEN_Win("FamilyTEXP", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
 
       Gincome.Caption = (rs("TotalAmount"))
       Gincome.Caption = Format(Gincome, "###,##0,#00.00")
    End With
    Set rs = Nothing
End Sub

Open in new window

0
Comment
Question by:Whing Dela Cruz
  • 4
  • 4
8 Comments
 
LVL 15

Expert Comment

by:JackOfPH
ID: 23688712
Private Sub Income()
Call OPEN_Win("FamilyTEXP", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where Date =#" & myDate & "#"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
 
       Gincome.Caption = (rs("TotalAmount"))
       Gincome.Caption = Format(Gincome, "###,##0,#00.00")
    End With
    Set rs = Nothing
End Sub
 

Open in New Window Select All
0
 
LVL 15

Expert Comment

by:JackOfPH
ID: 23688731
Just change query to this:

sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where myDateField >= #" & StartDate & "# and myDateField <= #" & EndDate & "#"
   
0
 

Author Comment

by:Whing Dela Cruz
ID: 23688955
Hi!
i used the code bellow but the message says " Line 1: Incorrect syntax near '#'
i tried to change #  to  '   then a messages says' Invalid use of null

here's the value of my field.. thanks

    [TheXcode] [char] (12) NULL ,
    [TheIncomDes] [varchar] (50) NULL ,
    [D_Date] [datetime] NULL ,
    [TheIncomSource] [varchar] (50) NULL ,
    [TheIncomAmount] [float] NULL ,

Private Sub Income() 'sum total TheIncomeAnount Field
Call OPEN_Win("FamilyTEXP", "Winpos")
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    'sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date >= #" & eFrom1 & "# and D_Date < =#" & eTO1 & "#"
    sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date >= #" & eFrom1.Value & "# and D_Date <= #" & eTo2.Value & "#"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
 
       Gincome.Caption = (rs("TotalAmount"))
       Gincome.Caption = Format(Gincome, "###,###,##0.00")
    End With
    Set rs = Nothing
End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:JackOfPH
ID: 23689868
If you are using SQL then try this...

sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date >= '" & cstr(eFrom1.Value) & "' and D_Date <= '" & cstr(eTo2.Value) & "'"
0
 
LVL 15

Accepted Solution

by:
JackOfPH earned 2000 total points
ID: 23689884
A revised version...
sql = "SELECT SUM(TheIncomAmount) as TotalAmount, D_Date FROM MyIncome where D_Date >= '" & cstr(eFrom1.Value) & "' and D_Date <= '" & cstr(eTo2.Value) & "'"
0
 

Author Comment

by:Whing Dela Cruz
ID: 23690063
if i used this
sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date >= '" & CStr(eFrom1.Value) & "' and D_Date <= '" & CStr(eTo2.Value) & "'"
a messages say's " Invalid use of null"
What does it mean?
thanks
0
 

Author Comment

by:Whing Dela Cruz
ID: 23690205
i tried also this command and it works
sql = "SELECT SUM(TheIncomAmount) as TotalAmount FROM MyIncome where D_Date>= '" & eFrom1.Text & " '"
 but if i add and D_Date<= '" & eTo2.Text & "'"
a messages says " invalid syntax near "02"
What shall i do now?
0
 

Author Closing Comment

by:Whing Dela Cruz
ID: 31549049
Thanks Jack, More power to you!  I've got your idea and now i used it in my project...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month19 days, 22 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question