Results 1 to 11 of 11
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Format calculated fields in query


    I assume this question comes up a lot. How do I format calculated fields in a query. Sometimes the property sheet on the right allows format. Other times it doesn't display anything. Is there a reference to a tutorial that can teach me the various ways to form in the calculation expression that I do or VBA. I have a couple of fields that doesn't display as I want it to.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the query, select the field, properties box, set the format.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. Surely you understand and know that the property box sometimes does not display anything, no selection able, when we are talking a calculated field in a query.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show an example of the fields involved and the calculation and format required.
    And the query sql.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why in query? Set formatting for controls on forms and reports.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    there is no selection...you write your own.

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I wrote Currency and it formatted the field. There is no option to select the decimals. I want to see two decimals on the report. On the report there is place in properties to set the decimals. I do but it doesn't show the decimals. Is there a way to enter the word currency in that empty field that it will format decimals?

  8. #8
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then if the property wont show that, then in the query field

    NewName: Format([fieid],"$0.00")

  9. #9
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I wrote Currency and it formatted the field. There is no option to select the decimals.
    in that case your calculated field is not typed as a decimal type but as a string, long, integer or byte - otherwise you would see the decimals property. This is probably why your report does not show decimals either.

    you can use the ccur function around your calculation to return a currency datatype which will add your $ sign whilst keeping it as a numeric datatype for subsequent calculations

    e.g.ccur(my calculation)

    but I agree with June, sort the presentation out in the form or report.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    The challenge is solved. I also wanted to have amounts showing Null on the reports where there is zero, but where there are amounts the format is currency(two decimals) without the symbol. I found some other forum where I learnt from the advice others shared(setting visibility true or false on report when zero), and ranman's advice solved half of our issue in the query.
    I prefer the Currency format (spaces between thousands) but without the symbol which is "R" in South Africa.

    We're learning. Ajax, I do want to understand how I "type a calculated field as a decimal type"? In tables all monetary fields are of data type currency. Quantities are of data type Number. When I do a calculated field how do I have an influence on the field type?

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    When I do a calculated field how do I have an influence on the field type?
    as I advised, use ccur, there is also clng, cdate and others

    I also wanted to have amounts showing Null on the reports where there is zero
    for this, use the format property. For numbers there are 4 sections - positive; negative;zero; null


    to show a zero as null

    0.00;0.00;""

    or

    0.00;0.00;"Null" to show the word


    to show a null as N/A

    0.00;0.00;"";"N/A"



    to show a message if null


    0.00;0.00;"";"Enter Price"

    to show negatives in red

    0.00;[Red]0.00;"";"Enter Price"

    and so on

    note that format properties in a query are not necessarily passed on to a form or report - a form or report will only pick these up when it is created based on the query

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

Similar Threads

  1. Use calculated fields of the same query
    By paultje_bos in forum Queries
    Replies: 1
    Last Post: 09-20-2016, 05:53 AM
  2. Update query with calculated fields?
    By shaunacol in forum Queries
    Replies: 9
    Last Post: 07-09-2015, 01:32 PM
  3. Format numbers for a calculated field in a query?
    By Access_Novice in forum Queries
    Replies: 2
    Last Post: 10-25-2014, 03:56 PM
  4. Replies: 12
    Last Post: 10-01-2013, 12:59 PM
  5. Replies: 2
    Last Post: 06-10-2012, 01:10 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