Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format in Query (03)

    I am having a challenge formating within a query.

    For example I should be able to use: MyNumber: Format([Field_1]),"XX,XX0.000") and have the displayed results display as 11,982.000

    Instead I end up with XX,XX11982.000

    Any ideas as to why?

    Thanks,
    John

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Format in Query (03)

    Try replacing the Xs with #s.

    MyNumber: Format([Field_1]),"##,##0.000")
    Regards
    John



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

    Re: Format in Query (03)

    John Hutchison has already posted the solution. To find out more, type the word Format somewhere in the Visual Basic Editor (for example in the Immediate window) and press F1. Read the help subject, and click See Also for related subjects.

  4. #4
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format in Query (03)

    Hans,

    Thanks for the insight on the "help" subject. It was an obvious error on my behalf ie XX vs ##.

    I am having a challenge formatting a text field to numeric. I can change the text to a number by using "val" but the catch is when I try applying a format to the expression. It goes from right aligned (signifying a number) to left aligned; containing the format.

    I have attached a sample db of what I have described.

    Thanks for taking a look,
    John
    Attached Files Attached Files

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

    Re: Format in Query (03)

    For that column change the value to Val(Amount) then change the format property to standard
    Attached Files Attached Files

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

    Re: Format in Query (03)

    In addition to Pat's reply: the result of the Format function is a text value, so it will be left-aligned. The Format property is used to specify the way a value is displayed, without changing the underlying value.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format in Query (03)

    Pat/Hans,

    Thank you for the information. I did see the "format" property and tried to dropdown selection but nothing appeared. I was not aware one could type the word "standard". I was under the impression that if the dropdown did not produce a list to select from then that option was not available.

    Regards,
    John

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

    Re: Format in Query (03)

    If Access cannot determine which options would be appropriate, it will not display a dropdown list. You can always manually enter a built-in format such as Standard, Percent, Short Date etc., or a custom format such as #,##0.00 or ddd mmm d, yyyy.

Posting Permissions

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