Showing "Text" number field with comma

I have a field in an Access table that is set to TEXT.  The records in the field are a combination of "N/A" and unformatted numbers (ie, 125874).  I need the field to have "N/A" when there is no applicable value but I would also like when there are numbers to show with commas (i.e. 125,874).  I don't need a $ sign and i do not need the numbers to add up, hence why a text field works.  the comma is just for display purposes.  Is there come INSTR formula I can use when a number exists in the field?
marku24Asked:
Who is Participating?
 
Nick67Commented:
Slightly differently
IIf(IsNumeric([fieldname]),Format([fieldname],"#,###"),[fieldname])

The idea is the same.
Test the value.  Ron is looking for N/A.
Since typos do happen, mine is looking for a number
Format an applicable value to "#,###"

Ron's is the cheaper thing to run if no typos ever happen, as [FieldName] = "N/A" is cheaper to run.
If a typo happens, things will likely go bang.
Mine is more expensive
IsNumeric([fieldname] is harder to do.
But it will never go bang, because it will only ever attempt to format a number.

Your call.

*composed between Ron's first and second post*
0
 
IrogSintaCommented:
You can use this expression for a calculated field in a query or textbox:
=IIf([FieldName] = "N/A", "N/A", Format(Val([FieldName]),"#,###"))

Ron
0
 
IrogSintaCommented:
This expression is probably better in case you have text other than "N/A" in your table:
=IIf(IsNumeric([FieldName]), Format(Val([FieldName]),"#,###"), [FieldName])

Ron
0
 
IrogSintaCommented:
Hey Nick, so we both had the same idea :-)  Though yours is more succinct because I forgot to remove the VAL function when I copied my formula over to my second post.  So just to be clear, marku24, both Scott and I agree you should use:

=IIf(IsNumeric([FieldName]), Format([FieldName],"#,###"), [FieldName])
0
 
marku24Author Commented:
Thank you both - worked perfectly!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.