Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Normal behaviour for Access? (.)

    I

  2. #2
    New Lounger
    Join Date
    Jul 2001
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normal behaviour for Access? (.)

    I tried this and it worked fine in Access97
    _________________________________________
    Sub test(myName As String)
    Dim rs As Recordset
    Dim flag As Boolean
    Set rs = CurrentDb().OpenRecordset("Table1")
    flag = False
    rs.MoveFirst
    Do While Not rs.EOF
    If rs!name = myName And IsNull(rs!Date) Then
    flag = True
    End If
    rs.MoveNext
    Loop
    If flag = True Then
    MsgBox "user logged"
    Else
    MsgBox "user not logged"
    End If
    End Sub
    _____________________________

    Is there a proble with your testing of the null?

    Regards

    Gary

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    The Netherlands
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normal behaviour for Access? (.)

    Date/Time fields are strange fields. As far as I know Access stores dates as numeric values. I think Access uses a specific value to indicate the date is not filled or NULL. That would explain why your method is not working.

    Why not simply adde an indication logged in (or logged out) to your tabel and update that one at login and logout?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Normal behaviour for Access? (.)

    Thank you for your replies,

    And yes, Bart, I thought of that, I added a true/false field and updated it when the operator was logged out, but it didn

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Normal behaviour for Access? (.)

    Dates are a kind of variant, and variants are allowed to hold Null as a value. Text fields can be empty, but not actually Null.

    Nulls wreak havoc on keys, especially unique keys. The problem is that you can't compare a Null to anything (which is why you have to use the IsNull function to test for null values), so Access can't handle unique keys with Nulls in them. In fact, if you test it, you'll find that you can enter the same record over and over as long as it has a null in it. I would not rely on the text field working, because if it's empty, you may have the same problem. If it contains a null string (""), then the unique key will work.

    A workaround is to have a different field in your key that will never be null. For example, you could have a yes/no field for LoggedIn and use that instead of the date as part of the unique key.

    Another option is to have a placeholder nonsense date like 1/1/90 (assuming you use mdyy format in your part of the world) and make that the default value of the field. That way, you would have a value for your unique key and it would work. You would have to allow for the nonsense date in your other queries and in forms and reports, but it would make your key work.
    Charlotte

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Normal behaviour for Access? (.)

    Understanding Null is one of the hardest things to comprehend. We think of Null as nothing, but really it may be easier to think of null as "could be anything". In that light, how could you include Null in a unique index? Since it could be anything, it might be a value you already have, but it might be something else!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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