Results 1 to 7 of 7
  1. #1
    Join Date
    May 2002
    Posts
    395

    Unanswered: formatting the values in % and align the text

    In my query, I formatted the values in a field in % but I also need them align to the right of the column in the table. The query I created is a Make Table Query. If I do the setting in the Format property of the field in the table it will not save; therefore, I ‘ll need to create the setting(s) in my SQL statement in the query.

    This is SELECT statement:
    SELECT date, A, B, Format ([quan]/[total], “Percent”) AS A,.. INTO tblMonth FROM tblA INNER JOIN tblA.Date = tblB.Date;

    What I need are the A and B values convert to % and also align to the right in the column of the table.

    Do I add another Format function in the query?

    Thanks!

  2. #2
    Join Date
    Oct 2002
    Location
    Sao Paulo - Brazil
    Posts
    16
    hi alicejwz,

    If you need only to view (not to calculate), try this:

    ..., format(format([quan]/[total], “Percent”),"@@@@@@") as A, ...
    Regards,

    Rogerio

  3. #3
    Join Date
    May 2002
    Posts
    395

    convert value and align text

    Hello Rogerio,

    Yes, I do believe I just want have the values to view only.
    Does the format(format([quan]/[total], “Percent”),"@@@@@@") align the values to the right? What is a @?
    I made the change in my query now it’s giving me a blank column.
    Can you tell me why it’s not working?

    I would like the values in % and then align to the right of the column.

    Thank you!

  4. #4
    Join Date
    Oct 2002
    Location
    Sao Paulo - Brazil
    Posts
    16
    HI alicejwz,

    Really sorry, I wrote wrong the sequence. Correct is:

    ..., format(format([quan]/[total], "0%"),"@@@@@@") as A, ...

    The @ means a blank space. With the line above, you will get a string with 6 caracters align to right. First, format transform the number in percent. After, the second format transform percent in string.

    Good weekend
    Regards,

    Rogerio

  5. #5
    Join Date
    May 2002
    Posts
    395
    I changed..., format(format([quan]/[total], "0%"),"@@@@@@") as A
    Now is giving me #Error in my field.

    Thank you so much for your help.
    Hope you have a great weekend!

  6. #6
    Join Date
    Oct 2002
    Location
    Sao Paulo - Brazil
    Posts
    16
    Hi alicejwz,

    It´s very strange. but see: if [total] is 0, the field will display "#Error". Otherwise it must work.

    Give another check. If isn´t your case, send me in pvt and i will take a look.
    Regards,

    Rogerio

  7. #7
    Join Date
    May 2002
    Posts
    395
    Good morning,

    Actually, I did made a mistake in the query.
    After reentering the correct expression I received this error message:

    Syntax error(missing operator) in query expression 'Format((Format[tblRGAquan].[Apple]/[tblShipments]/[numShipments],"0%), "@@@@@@") AS [Apple%] .....

    This is my complete query expression, I appreciate you're taking a look at it.

    Thanks!

Posting Permissions

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