Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139

    Unanswered: Code not working

    Hello;

    Can someone tell me why this code is not returning the desired result? DateOfResolution's default value is Date(). If someone removes the date I want the CloseTime and Status to act accordingly. The current code is not changing the CloseTime or Status when deleted.

    Thanks...
    Larry


    Private Sub DateOfResolution_AfterUpdate()

    If Me.DateOfResolution = Null Then
    Me.CloseTime = ""
    Me.Status = "Open"
    Else

    Me.CloseTime = Now()
    Me.Status = "Closed/Completed"
    End If

    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Have you tried

    IF IsNull(Me.DateOfResolution) Then

    or even

    Me.DateOfResolution & "" = ""


    MTB

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    What Mike is pointing out with the last option (which is correct) is that when deleting text for a textbox, you DO NOT end up with a Null string. You end up with an EMPTY string ... So your comparison failed because of that. And likewise, testing Isnull on an empty string will fail too. It's empty NOT NULL ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    just for the fun of it, you could also use
    if nz(Me.DateOfResolution, "") = ""
    or, even more explicit:
    if not isdate(nz(Me.DateOfResolution, ""))

    izy


    LATER: just in case, i thought i would mention that access doesn't understand dates or times - it has a single datetime data-type that combines date and time. if you are storing two fields, one for date, one for time, you are duplicating effort. in access (and many other things as well) a time is a datetime with zero date, a date is a datetime with zero time
    Last edited by izyrider; 10-20-06 at 12:47.
    currently using SS 2008R2

  5. #5
    Join Date
    Aug 2005
    Location
    Delaware
    Posts
    139
    Thanks guys... MikeTheBike's suggestion did the trick. And izyrider... I was aware of the single date/time, however converting to that format caused problems with reports and querys already developed. The two seperate fields works best for me in this instance.

    Thanks again...

    Larry

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    To cover all the bases I always use both of MTB's suggestions:

    Code:
    IF IsNull(Me.DateOfResolution) or Me.DateOfResolution & "" = "" Then
    
         Me.CloseTime = ""
         Me.Status = "Open"
    Else
         Me.CloseTime = Now()
         Me.Status = "Closed/Completed"
    End If
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if nz(Me.DateOfResolution, "") = "" then
    is probably faster than or-ing the two compares but comes to the same in the end.

    but there's a catch
    0z/02/2006
    is not null, not empty, not date
    which might upset date-expecting calculations elsewhere

    so
    if not isdate(nz(Me.DateOfResolution, "")) then
    seems to handle null, empty, false-dates.
    but even this will fail for full datetimes using 12-hour clock in A2K3 under Korean-WindowsXP

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I'll remember that the next time I write an app for the local Korean market!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    don't laugh too soon.

    i had zero plans for the Korean or any other market (this app is in english for internal use within my Co).
    -then my Co offshored the data handling.
    -this particular user was a Korean expatriate living in Malaysia: honestly not something that i had anticipated or planned for or had any experience of until the app fell over when she updated from Korean2K to KoreanXP.
    -diagnosing the problem was ...interesting.

    izy
    currently using SS 2008R2

Posting Permissions

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