Chart report leading zeros

[mulefeathers]

I have created a chart report based on a query that tracks the top 5 most expensive part numbers. The query returns the part numbers as they are and correct. Since some part numbers have both text and numbers, the field is formatted as text. when I preview the chart all leading zeros are removed even through they appear in the query.



Why and how do I fix this problem.

[RuralGuy]

The rule-of-thumb is "unless you will be performing math on the number, make it a text field". It sounds like somewhere you (or Access) have declared the value as numeric where leading zeros make no sense and are removed.

[mulefeathers]

As stated the fields are formatted as a text field. No math is being done. It is straight forward the table where the query gets the information from is imported from an excel file. The query returns the correct number the chart does not.

What type on an instance would access change a field type?

[RuralGuy]

Access likes to import numbers from Excel as a number. It could be the chart is forcing the value to a number. Try adding an _UnderScore to the beginning of the value in the query and see what happens.

[mulefeathers]

Adding _ lets me see the zeros but it the part number now has a _ in the chart. So that wont work, but thanks anyway.

[RuralGuy]

How about prepending a " "?

[TheShabz]

what I do when I need leading zeros is exporting to excel, formatting as needed, and making sure the first line contains some text. for example:
ProdID
test
001
002
003

Now when you import back, Access will see "test" as the first entry and treat the column as text. Then I go in and delete the record containing "test". its a quick and dirty way i use to fix it.

[mulefeathers]

I see the work around and that may be what I have to do, but I still would like to know why it is only happening in chart view. The part number column is formatted as text in the excel file as well as the table in access. The query returns the part numbers correctly including the leading zeros. It is only when I view it as a chart. The report that pulls from the same query (same set up it just returns all results not just the top five) displays the information correctly.

Anyway thanks for all the help.

As far the text most part number start with letters such as RF-123423456 so it should already recognize the text.

[RuralGuy]

As I implied in post #2, it is probably the Chart (and therefore Access) that it helping just a bit too much. Have you applied all of the Service Packs and HotFixes? That may have been fixed already.

[mulefeathers]

As I implied in post #2, it is probably the Chart (and therefore Access) that it helping just a bit too much. Have you applied all of the Service Packs and HotFixes? That may have been fixed already.

I will check and make sure we are up to date. Thanks