Page 2 of 2 FirstFirst 12
Results 11 to 15 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. #11
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,492
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

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

    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:

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

    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

  2. #12
    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
    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:

    Code:
    =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

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

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

    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.

    Code:
    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
    Last edited by xenou; May 12th, 2018 at 01:24 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

  4. #14
    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
    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.

    Code:
    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!

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

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

    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.
    Last edited by xenou; May 14th, 2018 at 09:27 AM.

    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

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
  •