Results 1 to 5 of 5
  1. #1
    TNK is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    3

    Unhappy Display a number from an expression in fixed format.

    Access 2016. Hello. Im trying to display a number from an expression in fixed format (2). I’m trying to get a fixed decimal (2) format generated from: z: IIf(IsNull([Field1]),"NM",[Field1]). When I just query on Field1, I correctly get (example) “10.00”. The expression works as intended, but I get a “10” instead of the 10.00. Field is Number, Double, Fixed (2) with a default = null/blank. Full Disclosure: Novice.

  2. #2
    TNK is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    3
    I got it ........ z: IIf(IsNull([Field1]),"NM", Format([Field1],"0.00"))

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    first - mixing text and numbers in the same field will lead to tears.
    since this is in a query, there is no need to state it here - forms and reports are where you format fields to look as you want. just use the format property which would be #.00 or #,##0.00 to display 2 decimal places. You can do this in a query as well, but generally better to see the actual data and apply the format in the form or report.

    You don't need your iif function, you can set the format property to display "NM" if null. Numbers have 4 format states - positive; negative; zero; null. So your format property could be

    #.00;#.00;0.00;"NM"

    you can also use some basic colors e.g.


    [green]#.00;[red]#.00;[blue]0.00;[magenta]"NM"

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    I got it ........ z: IIf(IsNull([Field1]),"NM", Format([Field1],"0.00"))
    that is fine if you don't want to do any maths with the value - the format function converts the number to text - and you can't add/subtract text. But at least the field is now all text.

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi!

    Format of the control that you want to display the number: 0.00;-0.00;0.00;"NM"
    Query expression: z: [Field1]

    Welcome!
    John

    Edit: Sorry, I post without refresh!...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-15-2016, 09:12 PM
  2. Replies: 5
    Last Post: 05-14-2014, 03:19 PM
  3. Generate Fixed Number of Entries
    By dustonabt12 in forum Reports
    Replies: 6
    Last Post: 07-10-2013, 01:55 PM
  4. Replies: 1
    Last Post: 04-29-2013, 11:05 AM
  5. Format decimal numbers with fixed width
    By Persist in forum Programming
    Replies: 5
    Last Post: 07-14-2010, 05:43 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums