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]

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.

 

[WolfLarsen85]

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

 

[xenou]

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?

 

[WolfLarsen85]

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

 

[xenou]

In the DSum() function there is a place for where criteria: DSUM(<field>, <table>, <where criteria>)

 

[WolfLarsen85]

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>

 

[xenou]

Do these tables have a date field in them:
tblLyftIncome
tblExpenses

What are the names of those fields?

 

[WolfLarsen85]

Do these tables have a date field in them:
tblLyftIncome
tblExpenses

What are the names of those fields?

Yes, "IncDate" and "ExpDate"

 

[Micron]

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.