Results 1 to 6 of 6
  1. #1
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41

    How to format a calculated field in the query grid?

    I am working in the query grid. TotalHours is a calculated field. SumOfdhHours and SumOfdhMinutes are numbers.

    My first go at it creates a number column in the Datasheet view but the totals are wrong because some fields are null.
    Code:
    TotalHours: [SumOfdhHours]+[SumOfdhMinutes]/60

    So I added the nz function. Now the totals are correct but need to be formatted.
    Code:
    TotalHours: Nz([SumOfdhHours],0)+Nz([SumOfdhMinutes]/60,0)

    I tried formatting but it produces a text field in the Datasheet view. I want a number field.


    Code:
    TotalHours: Format(Nz([SumOfdhHours],0)+Nz([SumOfdhMinutes]/60,0),"Fixed")

    Evidently I am misusing the Format function. What is the correct way to use the Format function to get a number field in the Datasheet view?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In query design view, you should be able to format the calculated field using the property sheet. However, if you are using queries to add/edit data, it is ill advised, and the normal approach would be to format your form controls/field(s), not the query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the format function returns a text value - as micron suggests, don't do it in the query - apply it as a property on a form control.

    alternatively, consider using the round function

  4. #4
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Thanks for the advice guys.

    I am developing the query to be the record source for a report.

    Thanks for pointing out that the Format function returns a text value -- I should have known that. I then tried the FormatNumber function in the query grid but it also yields text not numbers:
    Code:
    TotalHours: FormatNumber((Nz([SumOfdhHours],0)+Nz([SumOfdhMinutes]/60,0)),1,-1,-1,-1)
    Here is what I have settled on: Setting the format of TotalHours to General Number in properties and using this code in the query grid yields a number field with one digit to the right of the decimal point, which is what I was working toward:
    Code:
    TotalHours: Round(Nz([SumOfdhHours],0)+Nz([SumOfdhMinutes]/60,0),1)
    Appreciate the help.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    set the format in your report - not the query

  6. #6
    steve7800 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    41
    Thanks Ajax. That's the thing to do.

    Now the query grid has this code:
    Code:
    TotalHours: Nz([SumOfdhHours],0)+Nz([SumOfdhMinutes]/60,0)
    The TotalHours field is formatted in report properties for Format => Fixed and Decimal Places => 1. That was my initial goal

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

Similar Threads

  1. Replies: 5
    Last Post: 05-03-2019, 10:16 AM
  2. Replies: 5
    Last Post: 10-08-2018, 09:25 AM
  3. Format calculated fields in query
    By Perfac in forum Programming
    Replies: 10
    Last Post: 03-24-2018, 03:49 PM
  4. Reporting In Grid Format
    By DiscoverIT in forum Reports
    Replies: 4
    Last Post: 04-25-2017, 10:56 PM
  5. 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

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