Results 1 to 6 of 6
  1. #1
    Auto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2013
    Posts
    60

    Date diff on Forum

    hi,
    i am having an issue now on forum comparing dates, now issue is this

    Table Name is Transaction

    Fields are ID, CuDate, DaysEmpty, and some more.
    now i have a form for that table, user enters the date, and some more info, what i need is, i must have the MAX date from the table, and then check the diff between MAX date and Date entered on forum, and then put the number of days in field called DaysEmpty.

    so here is what i did

    Code:
    Private Sub CuDate_BeforeUpdate(Cancel As Integer)
    
    Dim LDate As String
    LDate = DMax("[CuDate]", "Transaction", "")
    End Sub
    so that brings me the MAX date from the table before the date from form, now after entering date from form here is the code

    Code:
    Private Sub CuDate_AfterUpdate()
    
    Dim HDays As Integer
    HDays = LDate - Me.CuDate
    Me.DaysEmpty= HDays
    
    
    End Sub
    i get an overflow error
    can someone have an idea how i can get that done?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,198
    For starters, LDate would only have a value within the first sub, since it's declared there. Second, if it's a date it should be declared as such.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Auto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2013
    Posts
    60
    thanks paul for your reply
    i will tell you the truth, i tried already 5 different options with no results, i posted the last one, and yes you are right on that, it was mistakes in it, but let me make it clear, why i did this, and what didnt help

    but first of all, LDate was wrong declared and i changed it to date, and still throws an overflow

    now in regards to the value in first sub, the reason why i went that way is, because i was out of options, i cant get MAX date in the AfterUpdate because it returns the same date as entered on form, and i need the last date entered before that date, so the only way i was able to get that, was in BeforeUpdate, now i know that i cant take a value from another sub especially when its private declared, and even i declare it public i am not getting the value, but i am trying to figure out how i can get that value saved and call it in the afterupdate so i would be able to get the results

    any suggestion?

  4. #4
    Auto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2013
    Posts
    60
    let me make it more simple;
    here is how the form look like


    Form
    CuDate> here goes the date of current transaction
    DaysEmpty> here goes the amount of days between CuDate and MAX Date from table


    how can i get that done?

  5. #5
    Auto is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jul 2013
    Posts
    60
    ok i got solved, very simple. (just used my brains) thanks all for trying to help

    Private Sub CuDate_BeforeUpdate(Cancel As Integer)

    Dim LDate As Date
    LDate = DMax("[CuDate]", "Transaction", "")

    'here i added
    me.DaysEmpty = LDate

    End Sub



    Private Sub CuDate_AfterUpdate()

    Dim HDays As Integer
    ' here i changed
    HDays = Me.CuDate - Me.DaysEmpty
    Me.DaysEmpty= HDays


    End Sub

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,198
    Glad you got it sorted. You should have been able to declare the variable at the top of the form module.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Converting dBase forum to Access forum
    By amerifax in forum Forms
    Replies: 5
    Last Post: 07-03-2012, 01:12 AM
  2. Replies: 1
    Last Post: 01-26-2012, 10:22 PM
  3. Different Prices for diff customers
    By kerrin in forum Access
    Replies: 4
    Last Post: 12-31-2011, 07:50 PM
  4. Date diff trouble
    By Cran29 in forum Queries
    Replies: 3
    Last Post: 03-11-2011, 01:02 PM
  5. Check time Diff.
    By wes028 in forum Access
    Replies: 3
    Last Post: 03-08-2010, 10:05 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums