Access to 3 decimals -- Formatting

Good morning, experts:

I need some assistance with fixing a "formatting issue" in a query.

Tables -- background:
- I have a lookup table that contains the following (sample) records:  A, B, C, D, E
- I have a data storage table which contains the following (sample) records:   A, C, E
- For each record (A, C, E), I have associated values, e.g.: 10.381, 20.450, 30.974

Query -- background:
- I use a "left join" and link the lookup table to the data storage tables.   This ensures that all five records (A, B, C, D, E) are listed in the query even though only 3 of them have numeric values.

Query Output (with 3 decimals including the .450 for "C"):
A    10.381
B
C    20.450          
D
E    30.974

Now, instead of showing blank values for B and D, I've created the following expression:

Test: IIf([Field1] Is Null,"---",[Field1])

The revised query output now looks like this:
Query Output (with 3 decimals include the .450 for "C"):
A    10.381
B         ---
C      20.45          
D         ---
E    30.974

As illustrated above, the display of the "20.450" has changed to "20.45".   In the query, I'm using Field Properties (right-click on query field) and select Format = "Standard" and Decimal Place = "3".

My question... how I can force to still display 3 decimals while also including the "---"?   Please keep in mind, 3 decimals are displayed if I just insert Field1 (w/o forcing to add the dashes).

Thanks,
EEH
ExpExchHelpAnalystAsked:
Who is Participating?
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.

mbizupCommented:
Try this:

Test: IIf([Field1] Is Null,"---",FormatNumber([Field1],3))
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
ExpExchHelpAnalystAuthor Commented:
mbizup:

Thanks... ok, they now display 3 digits but the values have become "text" vs. "numbers" (left-aligned vs. right).

Thus, any form of computation would now another step to change them back into numeric values.

Any changes they remain numeric values?

Thanks,
EEH
0
mbizupCommented:
Formatting the number like this will make it text.

Regarding the computations, they *should* implicitly be treated as numeric (no conversion necessary).  You just need to be careful to exclude "---" from computations.

EDIT:

An option may be to use the field's format properties to get the format needed, but I think the --- may be an issue if you try to do that.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ExpExchHelpAnalystAuthor Commented:
mbizup:

Some members will use Excel to do their analysis.   So, once the table/query results are copied from Access into Excel, they've become "text".

So, while your recommendation is very helpful, it just not yet the 100% solution I was after.

Do you have any additional recommendations on this?

Thanks,
EEH
0
mbizupCommented:
That would have been important information to include in the original question.  It certainly complicates things.  

Excel does its own conversions/formatting when data is transferred from Access to Excel.  I'm not sure how to handle that other than doing the formatting in Excel after the transfer (which can be automated from Access VBA, but it is code-intensive).
0
ExpExchHelpAnalystAuthor Commented:
mbizup:

Thanks... I hear you... I did not anticipate the data type would change (as part of any proposed solutions).

EEH
0
mbizupCommented:
A couple of other things to try:

-  Format the field in your query (using the format properties).  I believe this will keep the field numeric, but suspect that the trailing zeroes will be dropped on the Excel export (still worth a try, because it is a fairly simple solution).

- If that doesn't work, think of this as a two-step process.  I gave you one method to keep the trailing zeroes and the ---.   Try posting a separate question, explaining the problems you are encountering in Excel, and ask how to number-format this data in excel (either through column properties or by code in Access).  I'm personally not sure of the best way to handle it, but if you do post a separate question, be sure to include the Excel topic Area to get Excel experts looking at it too.
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.