Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Null problem (XP/2K)

    I have the following line of code:

    !curFixedAmount = Me.curPriceShekF + Me.curPriceShekM + (fGetYatzig() * Me.curPriceDollF) + (fGetYatzig() * Me.curPriceDollM)

    the me.* are unbound text boxes that can be null.
    the function fgetyatzig gets the exchange rate from NIs to $ from a table.
    If one of the text boxes is blank then I get the following error:
    "Run time error 13 type mismatch"

    How can I use the NZ function to get rid of this ? I have tried to use it and it does not help.
    Any ideas??
    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Null problem (XP/2K)

    You probably need to wrap each occurence in NZ()

    !curFixedAmount = NZ(Me.curPriceShekF) + NZ(Me.curPriceShekM) + (fGetYatzig() * NZ(Me.curPriceDollF)) + (fGetYatzig() * NZ(Me.curPriceDollM))


    HTH

    Peter

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null problem (XP/2K)

    I had tried and it did not help, but to be sure I tried what you had written and that also did not work.
    thanks for the try.

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

    Re: Null problem (XP/2K)

    Does this work? It is almost the same as Peter's solution, but explicitly sets the value to be used instead of Null.

    !curFixedAmount = NZ(Me.curPriceShekF, 0) + NZ(Me.curPriceShekM, 0) + (fGetYatzig() * NZ(Me.curPriceDollF, 0)) + (fGetYatzig() * NZ(Me.curPriceDollM, 0))

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null problem (XP/2K)

    I am about to leave the office. I will try this later
    Thanks Hans

  6. #6
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null problem (XP/2K)

    Still the same problem.
    Could it be the field in the original table which is currency cannot deal with this? (Although I cannot think of any reason why!)
    Thanks

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

    Re: Null problem (XP/2K)

    OK, one more thing to try:

    !curFixedAmount = CCur(NZ(Me.curPriceShekF, 0)) + CCur(NZ(Me.curPriceShekM, 0)) + fGetYatzig() * CCur(NZ(Me.curPriceDollF, 0)) + fGetYatzig() * CCur(NZ(Me.curPriceDollM, 0))

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null problem (XP/2K)

    YES!!!
    thanks Hans.
    What I learn from this is that each part of the function needs a ccur, and not a general one around the whole expression. Why?

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Null problem (XP/2K)

    The reason is that if any component of the expression equates to Null then the whole expression equates to Null.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Null problem (XP/2K)

    that explains why each part needs a NZ, but why can you not put a CCur around the whole expression?

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

    Re: Null problem (XP/2K)

    I don't know the explanation for sure, but sometimes NZ results in a string, even if you would expect a numeric result. If that is the case in your database, CCur forces the result to currency.

Posting Permissions

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