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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.