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,645
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

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,645
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,645
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,099,258
Messages
5,467,618
Members
406,545
Latest member
puneet829

This Week's Hot Topics

Top