Using Dates to grab data from a table to text boxes on a form? PLEASE HELP

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
At the end of the day you want something like this:

=DSum("Amount", "tblExpenses", "ExpDate between #1/1/2018# And #1/5/2018#")

Since you are building the string from other strings you have to be careful with the concatenation of all the elements, so this is along the lines of what you want:

Code:
=DSum("Amount", "tblExpenses", "ExpDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
=DSum("Income", "tblLyftIncome", "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
If your dates include dates with times ("1/1/2018 12:15 PM") the between might be a problem at the end boundary but if the dates are only whole dates ("1/12018") then its fine this way.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

WolfLarsen85

New Member
Joined
Apr 25, 2016
Messages
17
At the end of the day you want something like this:

=DSum("Amount", "tblExpenses", "ExpDate between #1/1/2018# And #1/5/2018#")

Since you are building the string from other strings you have to be careful with the concatenation of all the elements, so this is along the lines of what you want:

Code:
=DSum("Amount", "tblExpenses", "ExpDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
=DSum("Income", "tblLyftIncome", "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
If your dates include dates with times ("1/1/2018 12:15 PM") the between might be a problem at the end boundary but if the dates are only whole dates ("1/12018") then its fine this way.

I updated it to the following, but I get a null value. Is "ExpDate and IncDate supposed to have brackets? I have also formated all dates to short date to get ride of the time stamp.


Private Sub btnCalculate_Click()
Dim txtNetIncome As Currency
Dim txtIncome As Currency
Dim txtExpense As Currency
Dim txtBegDate As Date
Dim txtEndDate As Date

Me.txtIncome = DSum("Income", "tblLyftIncome", ("IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#"))
Me.txtExpense = DSum("Amount", "tblExpenses", ("ExpDate between #" & txtBegDate & "# And #" & [txtEndDate] & "#"))

Me.txtNetIncome = Me.txtIncome.Value - Me.txtExpense.Value


End Sub
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
One possibility is that you have no data that fits the criteria, so offhand I cannot tell if there is a problem or not. You need to know what your data is. You can use something like the following to find out what the textboxes really are doing in your code (report the results if you are still confused. You really should be providing sample data from the tables too, and giving precise information about all the datatypes - everything is in the details).

Also you should try to get this working outside of VBA. Just put a dsum function in a textbox control and play with it.

For what it's worth I did test my previous formulas and it worked so again - you have to look closely at your data, the datatypes, how the parameters are being passed to dsum, and what the expected results are. When you have problems you should always test with a simplified dataset that can be easily verified and checked.

Code:
Private Sub btnCalculate_Click()
Dim txtNetIncome As Currency
Dim txtIncome As Currency
Dim txtExpense As Currency
Dim txtBegDate As Date
Dim txtEndDate As Date

dim s as string	
s = [txtBeginDate]
msgbox "value of txtBegDate: " & s
s = [txtBeginDate]
msgbox "value of txtEndDate: " & s
s = "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#"
msgbox "value of first criteria string is: " & s
s = ""ExpDate between #" & txtBegDate & "# And #" & [txtEndDate] & "#""
msgbox "value of second criteria string is: " & s

Me.txtIncome = DSum("Income", "tblLyftIncome", "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
Me.txtExpense = DSum("Amount", "tblExpenses", "ExpDate between #" & txtBegDate & "# And #" & [txtEndDate] & "#")

Me.txtNetIncome = Me.txtIncome.Value - Me.txtExpense.Value


End Sub
 
Last edited:

WolfLarsen85

New Member
Joined
Apr 25, 2016
Messages
17
One possibility is that you have no data that fits the criteria, so offhand I cannot tell if there is a problem or not. You need to know what your data is. You can use something like the following to find out what the textboxes really are doing in your code (report the results if you are still confused. You really should be providing sample data from the tables too, and giving precise information about all the datatypes - everything is in the details).

Also you should try to get this working outside of VBA. Just put a dsum function in a textbox control and play with it.

For what it's worth I did test my previous formulas and it worked so again - you have to look closely at your data, the datatypes, how the parameters are being passed to dsum, and what the expected results are. When you have problems you should always test with a simplified dataset that can be easily verified and checked.

Code:
Private Sub btnCalculate_Click()
Dim txtNetIncome As Currency
Dim txtIncome As Currency
Dim txtExpense As Currency
Dim txtBegDate As Date
Dim txtEndDate As Date

dim s as string    
s = [txtBeginDate]
msgbox "value of txtBegDate: " & s
s = [txtBeginDate]
msgbox "value of txtEndDate: " & s
s = "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#"
msgbox "value of first criteria string is: " & s
s = ""ExpDate between #" & txtBegDate & "# And #" & [txtEndDate] & "#""
msgbox "value of second criteria string is: " & s

Me.txtIncome = DSum("Income", "tblLyftIncome", "IncDate between #" & [txtBegDate] & "# And #" & [txtEndDate] & "#")
Me.txtExpense = DSum("Amount", "tblExpenses", "ExpDate between #" & txtBegDate & "# And #" & [txtEndDate] & "#")

Me.txtNetIncome = Me.txtIncome.Value - Me.txtExpense.Value


End Sub

Ok, Thanks for the help, I really appreciate it!
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,670
Office Version
2013
Platform
Windows
Okay cool I wanted to make sure you have some ideas for debugging so progress can go faster. I can add one more also - you should be able to convert the dsum function to a sql query, since the where part of the function in dsum is basically just a where clause without the actual "WHERE" word in it.

In this case:
select sum(Amount) as SumOfAmount from tblExpenses where ExpDate between #1/1/2018# And #1/5/2018#

As you can see, the last argument to dsum is exactly everything after the where keyword.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,102,202
Messages
5,485,319
Members
407,496
Latest member
PttrsnMrgn

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top