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

WolfLarsen85

New Member
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

MrExcel MVP, Moderator
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:

WolfLarsen85

New Member
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

MrExcel MVP, Moderator
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

New Member
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

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

WolfLarsen85

New Member
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:

xenou

MrExcel MVP, Moderator
Do these tables have a date field in them:
tblLyftIncome
tblExpenses

What are the names of those fields?
 

Micron

Well-known Member
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:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top