UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Format With Comma For Thousands, Access 2013    
 
   
bakersburg9
post Nov 22 2017, 02:29 PM
Post#1



Posts: 5,231
Joined: 2-November 04
From: Downey, CA


I can't believe after lots of 'googling,' I couldn't find the syntax in an Access query to format a number in thousands .... I'm querying off my table for a total count of
CODE
DatePart("m",[Sched Dte])
records in the "Sched Dte" field - I have 1,410 records, but when I run my query it shows as 1410 .... I know this is a slam dunk for the UA gurus...
Go to the top of the page
 
kfield7
post Nov 22 2017, 02:35 PM
Post#2



Posts: 891
Joined: 12-November 03
From: Iowa Lot


in the query builder right-click in the lower field area, select standard format and the number of decimals (e.g., 0).


or adding a query field NewValue: format([YourField],"#,###") will return the value formatted as text.
Go to the top of the page
 
doctor9
post Nov 22 2017, 03:30 PM
Post#3


Remembered
Posts: 18,324
Joined: 29-March 05
From: Wisconsin


bakersburg9,

Also, if this query's data is going to show up in a report... don't bother trying to format the data in the query. Do so in the textbox on the report instead. It's much simpler. Remember that the Format() function returns a STRING value.

Hope this helps,

Dennis
Go to the top of the page
 
bakersburg9
post Nov 22 2017, 04:31 PM
Post#4



Posts: 5,231
Joined: 2-November 04
From: Downey, CA


query builder - sounds like a beginner level question. .. but where is that ? I see query wizard, query design....
Go to the top of the page
 
bakersburg9
post Nov 23 2017, 10:44 PM
Post#5



Posts: 5,231
Joined: 2-November 04
From: Downey, CA


So kfield, do I nest that in the middle of my formula, something like:

CODE
Totals By Month: format((DatePart("m",[Sched Dte],"#,###"))
??

I know that's probably not exactly right, but do I have the general idea ? I'm surprised there's not examples all over the 'net...

Steve
Go to the top of the page
 
kfield7
post Nov 24 2017, 12:13 PM
Post#6



Posts: 891
Joined: 12-November 03
From: Iowa Lot


Yes, and close.

And there are examples.

what you are formatting is the CountOf(DatePart...)

format(CountOf(DatePart("m",[Sched Dte])),"#,###")

But if you want it just in the query (as opposed to a report) I would use the query design (a.k.a. query builder) mode and format it there.

If you need a value, that will retain it as a value -- format, as Doctor9 said, returns a string or text.
Go to the top of the page
 
bakersburg9
post Nov 25 2017, 04:24 PM
Post#7



Posts: 5,231
Joined: 2-November 04
From: Downey, CA


Kfield,
What I should have said was that I was surprised I couldn't find any examples... that sight looks like a great resource - I've heard of it, but haven't used it much - thanks for your help ! cool.gif


Steve
Go to the top of the page
 
bakersburg9
post Nov 27 2017, 10:30 AM
Post#8



Posts: 5,231
Joined: 2-November 04
From: Downey, CA


Kfield - I got 'undefined function' when I made that change...
Go to the top of the page
 
kfield7
post Nov 27 2017, 12:54 PM
Post#9



Posts: 891
Joined: 12-November 03
From: Iowa Lot


My bad, trying to combine too many steps in my head to create aircode.
The CountOfXXX is what shows up as the field name when you do a count in a totals query.
You would create another query on the first query and format that field.
So in the first query you would do something like:

XXX: DatePart("m",[Sched Dte])

Make a totals query2 on this query1, you'll see CountOfXXX as the field name if you reference in a subsequent query.
Make a query3 on query 2, add a field:

YYY: format([CountOfXXX],"#,###").

there are certainly other approaches, and you may be able to combine 2 of these steps into one query as well.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2018 - 12:38 PM