Page 1 of 3 123 LastLast
Results 1 to 15 of 35
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Exclamation Unanswered: Format Numbers on a form

    Hello, everyone!

    I know it's a basic question, but I'm still having problems with it...

    I've got a form that is based on a query. That query calculates monthly costs on various cost centres, and I had to use the NZ() function a lot.
    Now, I'd love the numbers on the form have the standard number format and 0 decimal digits, e.g 34'567 and not 34567.98654.

    I tried right-clicking the text boxes, going to 'Options' and changing the Format there, but it doesn't work.

    Any suggestions??

    Thanks a lot in advance!

    Best,
    OfficeDummy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by OfficeDummy
    it doesn't work.
    Computer explodes? It returns dates in red text? Error message? Nothing happens? Changes format but not quite right?
    What expression did you use?

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    Quote Originally Posted by pootle flump
    Computer explodes? It returns dates in red text? Error message? Nothing happens? Changes format but not quite right?
    What expression did you use?
    What I meant is that the format isn't changed at all. In my query, I had a number 35679.87655 - and after 'formatting' the text box on a form I still get that hideous format.

    Maybe, the problem is that master query is based on a query, which is based on yet another query, which is based on yet another query....

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what format mask have you tried
    where have you placed it

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    You mean on a form?

    As I've said, I just right-clicked on the text box --> Options --> Format --> Standard Number, and I've also set the amount of digits after the point to 0.
    Like 324'543.

    I haven't done any formatting on a query.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can use the format function to write expressions (stuff the GUI). If you use that we can help you as you can paste what you've done here

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id suggest you try #,##0.00 as the format mask

    you may need to think what to do about rounding errors / remainders, as if you sum a series of numbers using the format mask then there is a risk that the totals may not be in agreement with the sum of the totals

    The decimal places property is not used if the number is 'standard number, you have to select fixed in place
    Last edited by healdem; 02-07-08 at 15:02.

  8. #8
    Join Date
    Jan 2008
    Posts
    74
    healdem, I followed your advice and put #,##0.00 as the format mask on all nine text boxes on the form. One of them is now formatted properly, but the other eight haven't changed at all. *puzzled*

    Can it be because I overused the nnz function? (The NZ didn't work for me, and I digged in Internet for any equivalent).

    The nnz function:

    Code:
    Function nnz(testvalue As Variant) As Variant
    'Not Numeric return zero
        If Not (IsNumeric(testvalue)) Then
            nnz = 0
        Else
            nnz = testvalue
        End If
    End Function

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ummm... if you're after no decimal places, that format is wrong. You'd need a format like #,##0

    And yes, that function is returning a Variant... perhaps you should force the function to return a number by changing "Variant" to "Double".

    Also bear in mind that no format is going to actually change a number's actual value. IE if you add them all up, full decimal precision will be added.

    The only way around that, and what I do to solve these situations, is to apply a Round function. You have to create the round function yourself since, amazingly, Access doesn't have a round function.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2008
    Posts
    74
    Oh.

    Thanks a lot! Assuming that I actually succeed in creating a Round function (which I highly doubt), my problem should be solved...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by StarTrekker
    The only way around that, and what I do to solve these situations, is to apply a Round function. You have to create the round function yourself since, amazingly, Access doesn't have a round function.
    Yes it does - and it's called Round()!

    BTW - I'm nervy that you need to use nnz - what values are not numeric? Is this actually a text column?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just a quick GOTCHA on the round function - it uses accountants rounding (I think it is called) not mathematical rounding.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Poots, for those niaive souls among us, what is the difference?
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2008
    Posts
    74
    No, the values were numeric. But the NZ function didn't work for me, neither did Iif.

    Erm... my version of Access (2003) doesn't recognise the Round() function. I get the #Name error.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Coo - can you post the exact expression. Round has existed as long as I can remember (admittedly alcohol related damage does not make this as impressive as it sounds). Please can you explain what was wrong with nz too? It might end up both are connected...

Posting Permissions

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