Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Is Null problem (Access 2000)

    I cannot define the Null situation in my code. I need the condition when the quantity in the field stock is Null.But i cannot do it.
    I can do it only with <=1 but then it means that the presence of 1 piece is not taken into account.
    If i write
    If CLng(DCount("stock", "Products", StrCondition)) = Null Then
    Or
    If CLng(DCount("stock", "Products", StrCondition)) = 0 Then
    Then the code does not function and no message box appears.

    If i write
    If CLng(DCount("stock", "Products", StrCondition)) < 1 Then
    Then the same happens again.

    My code works only when i put the following condition:


    If CLng(DCount("stock", "Products", StrCondition)) <= 1 Then
    MsgBox "Not on stock" ", vbInformation + vbOKOnly

    However, if i write <= 1, then if there is 1 piece on stock, the mesage says i have
    nothing on stock and the programme stops.
    How can i avoid this difficulty ?

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

    Re: Is Null problem (Access 2000)

    The function DCount always returns a number, so you cannot compare the result to Null. If

    If CLng(DCount("stock", "Products", StrCondition)) = 0 Then
    MsgBox ...
    End If

    does not display the message box, the conclusion must be that DCount is not 0, i.e. there is at least one record satisfying strCondition. Perhaps strCondition does not do what you intend. You haven't told us what the value of strCondition is.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is Null problem (Access 2000)

    Thank you for the attention.
    The StrCondition is as follows :

    Dim StrCondition As String
    Dim MySubform As Form
    Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
    StrCondition = "ProductID=" & MySubform.productid
    If CLng(DCount("branch0", "Products", StrCondition)) = 0 Then
    MsgBox " aha"
    End If
    The message box is not displayed,it follows something must be wrong.Actually, the relevant
    field branch0 for the productid is 0, but somehow my code cannot recognize it

  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: Is Null problem (Access 2000)

    >>If CLng(DCount("stock", "Products", StrCondition)) = Null <<

    Just a little something more beyond Hans' response. Your code above showed a basic misunderstanding of how Null works. Even if this was different (perhaps you used DLookup) such that a variant was returned, the use of =Null is always wrong!

    Let's take the simple situation of: If A = B

    If either A or B is null, the result is Null. In fact, even if BOTH A & B are Null, the result is Null; that is, even Null=Null yields a Null result, and not True.

    You must use the IsNull Function to evaluate Null, example: If IsNull(A)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Is Null problem (Access 2000)

    Is branch0 a field in the table products, holding the number in stock? My understanding is that it is.

    If so, you don't need to count branch0 just look it up.

    So try using

    If CLng(DLookup("branch0", "Products", StrCondition)) = 0 Then
    Regards
    John



  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Is Null problem (Access 2000)

    This is the solution.Thank you

Posting Permissions

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