Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2014
    Posts
    2

    Unanswered: Format a number without a decimal but including the decimal

    Hi

    I have a customer that wants a listing of our 5000 items and is requiring that several of the fields have the following format:

    Case Weight (6P1) Example 000051 = 5.1 lbs
    Case Height (6P1) Example 000025 = 2.5 inches
    Case Width (6P1) Example 000025 = 2.5 inches
    Case Length (6P1) Example 000145 = 14.5 inches

    All of these fields are number fields in my database and can contain multiple digits following the decimal. They only want one digit following the decimal (decimal removed) and max field length of 6 with leading zeros.

    Any idea how to write the formula to make Access output these in this way.

    Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    DisplayValue = Format(CaseWeight*10, "000000")
    Have a nice day!

  3. #3
    Join Date
    Jun 2014
    Posts
    2
    Thanks... That worked for items that have a value. However, items without a value are coming up blank any ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the nz function to handle NULLS, nz reaplces a null value with 0
    DisplayValue = nz(Format(CaseWeight*10, "000000"),"000000")

    of course you could alter your table design to stop nulls and set a default value of, say 0
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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