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: 4,953
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: 775
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


UtterAccess Editor
Posts: 17,924
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

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
bakersburg9
post Nov 22 2017, 04:31 PM
Post#4



Posts: 4,953
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: 4,953
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: 775
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: 4,953
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: 4,953
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: 775
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    14th December 2017 - 09:39 PM