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

[WolfLarsen85]

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.

 

[xenou]

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:

=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.

 

[WolfLarsen85]

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:

=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]

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.

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

 

[WolfLarsen85]

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.

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]

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.