Greetings.
Need a little help with Access 2002 numberic formatting.
I am using Access 2002, SP-2.
I want to run a Make Query table which draws its information from 10
already existing tables.
- The table will sort 5 ways, with the 5th sort in the first column.
- One of the tables contains prices, formatted and stored in 2
decimal places
Problem: When I run the query, the table sorts properly, but the
numeric values are not formatted properly.
I want 20 to show up as 20.00.
I have tried the following, but have been unsuccessful.
1. Append Query: Manually formatted the new table to Data Type=Number,
Field Size=Double, Format=Standard, Decimal Places=2. This solves my
formatting problem, but creates another. I am unable to sort on the
5th field, as this appears in the 1st column (and Access appears to
sort from left to right). Append does not give the option "not" to
show the field.
2. Make Table Query. Manually formatted fields on the new table, as
described above. Problem is the Make Table Query STEPS on this
formatting when it runs and 20.00 shows up as 20 (despite 20.00 being
stored in the table it is coming from).
3. Make Table Query: Have formatted the query for the affected fields
to the desired format (Properties->Format).
4. Haved tried 2 in conjunction with 3 (as described above).
This is driving me bonkers.
Can I get brief explanation on how to solve this problem, along with a
short discussion of why Access does this.
Many thanks for your help.
If any part of this explanation is unclear, please let me know and I
will clarify. |
Request for Question Clarification by
hammer-ga
on
18 May 2003 07:16 PDT
Respree,
Your question is a bit convoluted, so I'm not sure what method you are
currently using, or where the incorrect values are appearing (query
output or in a made table), but try this...
If the numeric field is in tblMyTable and is called MyField then,
Expr1:Format([tblMyTable].[MyField], "0.00")
The Format function should force your value into the format you want.
If this doesn't work, could you post a trimmed copy of your mdb where
I could get to it, or at least post the text of your query where we
can see exactly what you are trying to do?
- Hammer
|
Clarification of Question by
respree-ga
on
18 May 2003 10:37 PDT
Hi Hammer:
Here's a condensed version of what I'm trying to do.
http://www.respree.com/temp/accessprob.html
Unfortunately, your formula is now turning my value in text. I should
have been clearer that the answer needs to remain in a *numeric*
format with 2 decimal places.
Sorry the question was so complicated, but I had to the mention the
part about the sorting issue in order to avoid an answer about using
an Append Query (as it would solve this problem, but creating a
sorting problem).
Thank you for your help.
|
Request for Question Clarification by
hammer-ga
on
18 May 2003 14:53 PDT
Respree,
A computer will always store a numeric value with the number of
decimal places required by its internal data type. A double will store
20 as 20.000000000000000. An integer will store 20 as 20. Only
formatting causes it to *show it to you* as 20.00 or 20.000 or $20.00
or 20. When you format a table field with two decimal places, you are
only changing the output format, not the actual stored value.
I'll take a look at your mdb tomorrow, unless someone else beats me to
it, but you may want to take the above into consideration.
- Hammer
|
Request for Question Clarification by
hammer-ga
on
19 May 2003 07:42 PDT
Okay, I see what you are doing and the results are as they should be.
You feed it 20, it stores 20. Tables aren't really meant to provide
formatting of your values. Their main function is storage.
I'm concerned about your description of your project. You really
shouldn't need to care how the raw table values are formatted or what
order the records appear in the table itself. Tables are meant to
store raw data in random order. Forms and Queries and Reports are used
to sort and format as needed. Normally, you manage sort order and
output format when you pull the values out of the table.
What are you trying to do that requires the numbers to be formatted a
particular way in raw table view? Or that the records be sorted a
particular way? You may be able to solve your problems simply by
running a query against your new table that sorts the records and
formats the values.
- Hammer
|
Clarification of Question by
respree-ga
on
19 May 2003 09:06 PDT
Hi Hammer:
Thanks for your comments and efforts to help me.
I guess it really doesn't matter how the data is stored in the table.
However, the desired end-result is that I want to export this table to
a tab delimited text (.txt) file with (in this case) 20.00 as the
value in the field being discussed.
This text file will be imported into a MYSQL database that controls a
page this looks like this (notice the 20.00).
http://www.respree.com/product/pages/sku/LENO2062
Maybe I'm going about this the wrong way and the question would be be
presented as "how can I export a table so that numeric fields show up
on a tab delimited file formatted in 2 decimal places." The web page
above shows as 20 (rather than 20.00 if the imported .txt file
(exported from Access) shows 20 (rather than 20.00).
I hope that clarifes things a bit, but please let me know if my
intentions are still unclear and thanks again for your help.
Any suggestions?
|
Request for Question Clarification by
hammer-ga
on
19 May 2003 09:26 PDT
The correct place to have this reformatting happen is when the page is
built. The page building code should get 20 from MySQL and reformat it
as $20.00 at that point. Is this possible?
- Hammer
|
Clarification of Question by
respree-ga
on
19 May 2003 09:51 PDT
Yes, I'm sure it possible have the formatting take place when the file
imports to to MySQl, but I'm afraid my knowledge of MySQL is weaker
than it is for Access (which is pretty weak as well).
Is it not possible to export the file in the desired format (2 decimal
places) through Access? For my particular situation, it seems like
its the path of least resistance at this point.
|
Request for Question Clarification by
hammer-ga
on
19 May 2003 10:25 PDT
Try this:
1. Run your Make Table allowing Access to simply write 20 to the PRICE
field.
2. Create a Select query based on the newly made table that selects
the fields you want. Sort as needed.
3. In the Select query, right-click on the Price column and choose
Properties. Set Format to Fixed and Decimal Places to 2.
4. Save the Select query.
Now, export the Select query instead of the Table itself. Let me know
how that works. I tried it here on the one field you gave me and it
worked.
- Hammer
|
,
0 Comments
)