Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    10

    Unanswered: DateDiff() returns value 0?

    I have the following code:

    Private Sub chkOnLeave_Click()

    If Me![chkOnLeave] = -1 Then Me![chkActive] = 0
    Me![chkSeasonal] = 0

    Dim DaysDiff As Integer
    Dim Message, Title, Default, OnLeaveStartDate
    Dim Message1, Title1, Default1, OnLeaveEndDate

    Message = "Enter leave start date" 'Set prompt.
    Title = "Starting Leave Date" ' Set title.
    Default = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveStartDate = InputBox(Message, Title, Default)

    Message1 = "Enter leave ending date" 'Set prompt.
    Title1 = "Ending Leave Date" ' Set title.
    Default1 = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveEndDate = InputBox(Message1, Title1, Default1)
    Me.DaysDiff = DateDiff("d", [OnLeaveEndDate], [OnLeaveStartDate])

    End Sub

    The table which receives this data, correctly shows the dates entered, but the field "DaysDiff" shows only a 0 (zero) instead of the number of days.

    i.e. I input 7/22/2009 for OnLeaveStartDate and 8/22/2009 for OnLeaveEndDate, returns 0.

    If I create a query, with the following statement:

    OnLeaveDays: DateDiff("d",[OnLeaveStartDate],[OnLeaveEndDate])

    This query will returns the correct value of 30 days.

    So what am I missing in my code to get the value of 30 in the table under the field DaysDiff?

    All the best,

    Mahonri1
    Last edited by Mahonri1; 06-22-09 at 23:38.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    You are missing a [ on the OnLeaveStartDate]

    It should be
    Me.DaysDiff = DateDiff("d", [OnLeaveEndDate], [OnLeaveStartDate])

  3. #3
    Join Date
    Jun 2009
    Posts
    10

    Not an error

    Sorry, I mistakenly left the left bracket out of the post only.

    It's in the code correctly.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Try
    me.daysdiff = DateDiff("d", [OnLeaveStartDate], [OnLeaveEndDate])

    using your code I got a negative number

  5. #5
    Join Date
    Jun 2009
    Posts
    10

    Been there done that

    Doesn't work either way. Putting OnLeaveStartDate in first with OnLeaveEndDate still nets a 0 (zero) in the table field DaysDiff.


  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Mahonri1
    I have the following code:

    Private Sub chkOnLeave_Click()

    If Me![chkOnLeave] = -1 Then Me![chkActive] = 0
    Me![chkSeasonal] = 0

    Dim DaysDiff As Integer
    Dim Message, Title, Default, OnLeaveStartDate
    Dim Message1, Title1, Default1, OnLeaveEndDate

    Message = "Enter leave start date" 'Set prompt.
    Title = "Starting Leave Date" ' Set title.
    Default = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveStartDate = InputBox(Message, Title, Default)

    Message1 = "Enter leave ending date" 'Set prompt.
    Title1 = "Ending Leave Date" ' Set title.
    Default1 = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveEndDate = InputBox(Message1, Title1, Default1)
    Me.DaysDiff = DateDiff("d", [OnLeaveEndDate], [OnLeaveStartDate])

    End Sub
    You dim a variable "DaysDiff" as an integer, but then it appears that's also the name of your Textbox; I believe Access may be thinking it's a variable, assigning the variable the value, and ignoring that you have a textbox of the same name. Either remove the dim statement, or rename the variable and assign the calculated diff to the variable, and then assign the variable to the textbox.

    As a tip, I ALWAYS name things with the first three letters signifying what it is:
    tbo - textbox
    lbo - listbox
    cbo - combobox
    cmd - command button
    int - integer
    str - string
    frm - form
    tbl - table
    fld - field
    etc...
    So for example:
    dim strSQL as string
    dim intCount as integer
    dim intDaysDiff as integer
    etc....
    This practice ensures a few things: (1) I don't use any Access Reserve words by accident, (2) I don't assign the wrong value type to a variable (e.g., string to an integer, etc.) and (3) that I don't name two things the same thing (as in this case). And that way I always know what a variable is just by looking at it, and I don't have to go looking to see what I made it. Just a suggestion, take it as you like.
    Last edited by nckdryr; 06-23-09 at 00:38.
    Me.Geek = True

  7. #7
    Join Date
    Jun 2009
    Posts
    10

    Thumbs up Resolved with...

    Here is how I resolved it

    Private Sub chkOnLeave_Click()

    If Me![chkOnLeave] = -1 Then Me![chkActive] = 0
    Me![chkSeasonal] = 0

    Dim DDDiff As Integer
    Dim Message, Title, Default, OnLeaveStartDate
    Dim Message1, Title1, Default1, OnLeaveEndDate

    Message = "Enter leave start date" 'Set prompt.
    Title = "Starting Leave Date" ' Set title.
    Default = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveStartDate = InputBox(Message, Title, Default)

    Message1 = "Enter leave ending date" 'Set prompt.
    Title1 = "Ending Leave Date" ' Set title.
    Default1 = Date ' Set default.
    ' Display message, title, and default value.
    Me.OnLeaveEndDate = InputBox(Message1, Title1, Default1)
    Me.DaysDiff = DateDiff("d", Me.OnLeaveStartDate, Me.OnLeaveEndDate)

    End Sub

    Thanks for the instruction.

  8. #8
    Join Date
    Mar 2009
    Posts
    37
    thanks for your help! worked a treat!

Posting Permissions

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