Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: about NZ() function

    I have a question about the nz() function. When the first record is null in the database, we can use nz() function to let the receiptNumber =0. I use this code, RMax=Nz(DMax('ReceiptNumber", "tblReceipt"))+1, it works in my old pc. For the first record, it did get 1 for RMax, But for the other PC, it didn't get the 1, How can i handle the null value. I tried RMax=Nz(DMax('ReceiptNumber", "tblReceipt"),0)+1. It still didn't get the 1 for the first record. Thanks.

  2. #2
    Join Date
    Nov 2003
    Posts
    1,487
    Hmmm....First off your code line should really look like this:

    RMax=Nz(DMax("[ReceiptNumber]", "[tblReceipt]"), 0) + 1

    not

    RMax=Nz(DMax('ReceiptNumber", "tblReceipt"),0)+1

    I don't understand why you have the single quote ( ' ) or hyphon within your DMax function statement.

    Also....because we are using the Nz() function, we're expecting to deal with a Null value. This won't happen if the table field ReceiptNumber contains a Default Value of 0 (zero).

    Look into the tblReceipt table and check the Default Value property for the ReceiptNumber field. If there is a 0 (zero) there then remove it and try again.

    If you're bent on having the Default Value of Zero then simply don't use the Nz() function.

    m(o_o)m
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  3. #3
    Join Date
    Mar 2004
    Posts
    660
    I didn't use default value. I got it work. But i use :

    RMax=Nz(DMax("ReceiptNumber", "tblReceipt"))+1. It works. I don't know why. You said i should use "[ReceiptNumber]". I think this is the name of field. So we use "ReceiptNumber". Am i right? Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Quote Originally Posted by yyu
    I didn't use default value. I got it work. But i use :

    RMax=Nz(DMax("ReceiptNumber", "tblReceipt"))+1. It works. I don't know why. You said i should use "[ReceiptNumber]". I think this is the name of field. So we use "ReceiptNumber". Am i right? Thanks.
    Glad you got it to work...and yes...it is the name of the Field

    Either or these should work fine:

    RMax=Nz(DMax("ReceiptNumber", "tblReceipt"))+1

    or

    RMax=Nz(DMax("ReceiptNumber", "tblReceipt"),0)+1

    and in your specific case either one of these will work fine as well:

    RMax=Nz(DMax("[ReceiptNumber]", "[tblReceipt]")) + 1

    or

    RMax=Nz(DMax("[ReceiptNumber]", "[tblReceipt]"), 0) + 1

    It is (in my opinion) a good idea to tell the Nz() function what to Return if Null is encountered. And again in your case this would be 0.

    It is (again in my opinion) good practice to use the square brackets around Field and Table names when used in code or SQL strings and if not only for the reason that there may come a time when one of your Field Or Table names are not one word but perhaps two...or three.

    For example: "ReceiptNumber" and "Receipt Number".

    Notice that the second Field name consists of two words. If entered in this fashion into the DMax() Function, the function will fail miserably and a 3075 error will occure (Syntax Error (Missing Operator) in query expression).
    Now if "[Receipt Number]" was supplied to the DMax() function, all will work fine.

    For Table names, you can actually get away with names containing spaces but not in all cases. It's a good idea to place the square bracket around table names with spaces as well.

    The bottom line is this....if your Field or Table names contain spaces then enclose them in the square brackets [ ] when using them. A simple solution to al this is.....don't ever use names that contain spaces in them.

    The reason why I made the comment in the first place is....take a real close look at your original Post....

    you supplied us with:

    RMax=Nz(DMax('ReceiptNumber", "tblReceipt"))+1

    and then you provided yet another example with:

    RMax=Nz(DMax('ReceiptNumber", "tblReceipt"),0)+1

    Can you see the apostrophe (red)....within both samples?
    I just simply don't like it, but then again....that's just my opinion.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Mar 2004
    Posts
    660
    Thank you very much! I got it. This is really good point. Thanks again!

Posting Permissions

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