Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Using Dates to grab data from a table to text boxes on a form? PLEASE HELP
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.


  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,466
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    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 by xenou; May 8th, 2018 at 03:46 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,466
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

    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?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by xenou View Post
    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

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,466
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by xenou View Post
    In the DSum() function there is a place for where criteria: DSUM(, , )


    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
    Last edited by WolfLarsen85; May 9th, 2018 at 03:05 PM.

  8. #8
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,466
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

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

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

    What are the names of those fields?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  9. #9
    New Member
    Join Date
    Apr 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by xenou View Post
    Do these tables have a date field in them:
    tblLyftIncome
    tblExpenses

    What are the names of those fields?

    Yes, "IncDate" and "ExpDate"

  10. #10
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,698
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Micron; May 11th, 2018 at 10:16 AM. Reason: added info
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •