Requiring query expression that outputs whole numbers in format ".0"

[skydivetom]

Experts,



I need help tweaking a query to calculate an AVG output in numeric format with 1 decimal place, including for whole numbers.

Attached: ACCDB & illustration.

Issues:
✅ Query_Average_1_Text – Outputs {3.0, 4.0, 5.0} but as text.
❌ Query_Average_2_Numeric – Outputs {3, 4, 5} as numeric but without decimals for whole numbers.

Goal: Modify Query_Average_2_Numeric so SCORE remains numeric AND displays whole numbers as {3.0, 4.0, 5.0}.

I've tried multiple formats but none worked. Please provide only the actual solution (expression).

Thank you,
EEH

[Gicu]

Tom,
You can set the format in the Properties window of the column in query design view:

[skydivetom]

Vlad -- thank you for the response. For background purposes, I actually did use the suggested query properties to get to the desired format.

However, I use VBA (via form) to re-create the query based on different user inputs. Thus, each time when the query is re-generated, the query lost the "Fixed" (1 decimal) settings/properties. Hence, I'd like to include the correct expression into my VBA code to resolve this issue.

Given this forum is focused on "queries" (not programming), I simply chose to provide a query (vs. VBA) example. Anyhow, below is the VBA element (with slightly different format and field references). That's from my actual application (vs. using simplified demo version).

strSQL = "SELECT Q100_Scoring_Input.TIER_1_ID_H, Q100_Scoring_Input.HYPOTHESES_H, " & _
                             "ROUND(Avg(Q100_Scoring_Input.[" & strModel & "_SCORE]), 1) AS [AVG_" & strModel & "_SCORE] " & _
                             "FROM Q100_Scoring_Input " & _
                             "GROUP BY Q100_Scoring_Input.TIER_1_ID_H, Q100_Scoring_Input.HYPOTHESES_H " & _
                             "ORDER BY ROUND(Avg(Q100_Scoring_Input.[" & strModel & "_SCORE]), 1) DESC;"

Any thoughts/recommendation with tweaking it to output the desired format?

[Gicu]

Hi Tom,
How do you use the strSQL? Do you save it as a query? You can manipulate the format property of a querydef using DAO from VBA, here is one example:
https://www.access-programmers.co.uk...gh-vba.319253/
Cheers,

[CJ_London]

Perhaps surround the format function with the cDbl or eval function

[NoellaG]

also see https://www.accessforums.net/showthr...565#post531565

[skydivetom]

Thus far, it seems like all methods are either a) string with whole numbers shown as, e.g., {3.0, 4.0, 5.0} OR b) numeric values where whole numbers only show {3, 4, 5, etc.}. This includes, e.g., using cDbl, etc.

Looks like I need to do more research...

[madpiet]

Oh super awesome. </sarcasm>

If you use FORMAT to do it, the Format function returns a string, not a number. So this will make your numbers look pretty, but they will no longer be numbers, but text strings:

SELECT TeamResults.ID, TeamResults.EventDate, TeamResults.Teams, FORMAT(TeamResults.Player1,"0.00") AS FormattedPlayer1
FROM TeamResults;

[jojowhite]

You Format it on the Query designer not in the Column:


or you can use code on the demo datasheet form (jojo_demoDatasheet) i provided on the attached.
initially when i created this form, the Recordsource is Query_Average_2_Numeric.
then i remove the recordsouce to make it Unbound.
next i set the Recordsource on the Load Event of the form, with somewhat
similar Query string you have on your post.
then set up the Properties of AVG_SCORE textbox.
Note also that I am able to Total the column.

[Welshgasman]

You Format it on the Query designer not in the Column:


or you can use code on the demo datasheet form (jojo_demoDatasheet) i provided on the attached.
initially when i created this form, the Recordsource is Query_Average_2_Numeric.
then i remove the recordsouce to make it Unbound.
next i set the Recordsource on the Load Event of the form, with somewhat
similar Query string you have on your post.
then set up the Properties of AVG_SCORE textbox.
Note also that I am able to Total the column.

O/P was already advised of that in post #2

[Gicu]

We still don't know what happens to the strSQL in the end (how is its output displayed), is it used as recordsource for a form or report, saved as a query that then gets exported, etc...
Here is another post showing how to change the format using DAO once the strSQL is saved (using a querydef object, the example below is for a table but it is the same idea).
https://stackoverflow.com/questions/...ype-and-format
Cheers,

[jojowhite]

O/P was already advised of that in post #2

I did not see any Form on his attachment, did You?

[ArviLaanemets]

I suspect, OP creates a query string in form, and after that simply runs this query from there using the command - and wants to be the query result table field to be formatted having the returned query table field default format changed instead general to numeric with 1 decimal)!

[Gicu]

Here is a sample using VBA DAO to set the DecimalPlaces property of the field.

Cheers,

[Welshgasman]

I did not see any Form on his attachment, did You?

Gicu offered setting format in query field properties as did you?