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

WolfLarsen85

New Member
Joined
Apr 25, 2016
Messages
17
Hi,
I have created an income statement form in access that has a Beginning Date, End Date text boxes followed by Income and Expense text boxes and lastly and Net Income text box. I have tables set up for income and expenses. What I want is to enter a beginning and end date on the form and have the Income and Expense txt boxes to populate with the proper numbers pulled from the tables for the dates entered. I have set up queries that will do this separately, but the form will not pull the numbers. The calculation button for net income works already and I have used the DSum function that will pull the numbers from the queries but not by date. Could someone please help me get this date issue solved. I can not seem to figure it out. Thanks you in advance!!! can not seem to figure it out. Thanks you in advance!!! See below.

1601922914674.png
 
Last edited by a moderator:
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.
 
Upvote 0

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top