Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use of Null - Access 97 vs2002?? (Acccess 2002)

    I Converted an Access database from 97 to 2002. All code works fine except the following:

    Private Sub btnCertLtr_Click()
    On Error GoTo Err_btnCertLtr_Click

    Dim db As Database
    Set db = CurrentDb()

    DoCmd.OpenReport "rpt_CertifiedLtr", acPreview
    db.Execute "UPDATE certify " & _
    "Set certify.CertSent= date()" & _
    "where certify.CertDate<= date() AND (certify.CertNum <> Null) AND " & _
    "(certify.CertSent=Null) AND (certify.status='CH');"

    db.Close
    Call UpdateLtrCnts
    Me.Refresh

    Exit_btnCertLtr_Click:
    Exit Sub

    Err_btnCertLtr_Click:
    MsgBox Err.Description
    Resume Exit_btnCertLtr_Click

    End Sub


    This is just a basic routine that prints letters, then puts the current date in the CertSent field (meaning the letter was printed on this date). Works great in Access 97, but after converting to Access 2002 the letters print fine, but the current date will not be populated in the database! Is the way I use Null the problem? Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use of Null - Access 97 vs2002?? (Acccess 2002)

    Welcome to Woody's Lounge!

    Instead of certify.CertNum <> Null, try

    certify.CertNum Is Not Null

    and instead of

    certify.CertSent=Null

    try

    certify.CertSent Is Null

    Null is not a specific value such as 0 or 37, so you cannot use = and <> to test if a field is Null or not.

  3. #3
    New Lounger
    Join Date
    Jun 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use of Null - Access 97 vs2002?? (Acccess 2002)

    Bingo!! Works Great, Thanks.

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

    Re: Use of Null - Access 97 vs2002?? (Acccess 2002)

    Just to expand a bit on Hans' response.

    Null can be a hard concept to grasp. Actually, rather than think of it as "nothing", I believe it is best to think of it as "could be anything". This explains why you can't to any comparisons against Null; because if we can't determine what Null is, how can we compare it to anything?

    So remember, any comparison against Null produces a Null response. Even "If Null=Null" produces a Null! So if you want to check for Null, use "Is Null" or the IsNull() function.
    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
  •