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:

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Can you provide more information on your calculation button and/or dsum functions. Most likely the answer will be something modified in one of those. But the modifications are hard to suggest not knowing what you have already tried, in order to give advice on what to change.
 
Last edited:
Upvote 0
The DSum I used was only in the Expense and Income text boxes and I linked to the tables I had created, each text box separately. I would like to create a button with VBA code behind it that links to the Income and Expense text boxes after I enter the dates. I created a query that pulled the info when I entered the dates, but then the form would not pull the data from the query. The calculation buttons just does simple arithmetic subtracting expenses from income.

Private Sub btnCalculate_Click()
Dim txtNetIncome As Currency

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


End Sub
 
Upvote 0
It sounds like you need to add date criteria (the dates in the textboxes) to your dsum totals. What have you tried so far? Did you try anything at all yet to get the date criteria in them?
 
Upvote 0
It sounds like you need to add date criteria (the dates in the textboxes) to your dsum totals. What have you tried so far? Did you try anything at all yet to get the date criteria in them?

Ok, I have updated and am now showing my DSum functions. How to I add date criteria to these?

Private Sub btnCalculate_Click()
Dim txtNetIncome As Currency
Dim txtIncome As Currency
Dim txtExpense As Currency

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

Me.txtIncome = DSum("Income", "tblLyftIncome")
Me.txtExpense = DSum("Amount", "tblExpenses")

End Sub
 
Upvote 0
In the DSum() function there is a place for where criteria: DSUM(<field>, <table>, <where criteria>)
 
Upvote 0
In the DSum() function there is a place for where criteria: DSUM(<field>, , <where criteria="">)



Ok, updated it to this. This is where I get stuck, for some reason the "between" subfunction is not recognized. Do I have the syntax wrong? I am a beginner at this, so thank you for your help!

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.txtNetIncome = Me.txtIncome.Value - Me.txtExpense.Value

Me.txtIncome = DSum("Income", "tblLyftIncome", between("txtBegDate" And "txtEndDate"))
Me.txtExpense = DSum("Amount", "tblExpenses", between("txtBegDate" And "txtEndDate"))


End Sub</where>
</field>
 
Last edited:
Upvote 0
Do these tables have a date field in them:
tblLyftIncome
tblExpenses

What are the names of those fields?
 
Upvote 0
the whole criteria expression has to be enclosed in parentheses.
Not between("txtBegDate" And "txtEndDate") , which in addition to the misplaced parentheses is coercing txtBegDate to be a literal string, not a field name, but
"between(txtBegDate And txtEndDate)" or
"between txtBegDate And txtEndDate"

BTW, dates have to be in US format, or else you will need to include the format function in your criteria string.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,020
Members
448,543
Latest member
MartinLarkin

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