Numeric Values Truncating in Text Field

[Carlee]

Hello,
I have a test case ID number that is alpha numeric. (100.10, 100.20 etc).

Problem: when this field is printed in a report or exported to MS Excel,
the '0' in the decimal section of the Test Case ID number is being truncated.
So, 100.10 is appearing as 100.1. This is problematic because there is a
test case 100.1 and the two are getting confused.

Can someone please advise how to correct this problem?

 

[Al Campagna]

Carlee,
Access doesn't have an AlphaNumeric field type.
It's either Text (proobably what you mean) or Numeric.
Your ID field should be designated as a Text in your table. If it is,
then all ID values will retain either leading or trailing 0's.

Is there any chance that, for the report, you're using something like
Val(ID) to force proper sorting of the ID by the whole number portion of ID.
If so, the ID will drop beginning and trailing 0's.

I can't speak to the Excel issue, but it sounds like... if you solve the
Access report problem, the Excel will straighten out too.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Duane Hookom]

I'm not sure what you mean by "alpha numeric". If you wish to retain the
trailing 0s, you should be using a text field or possibly two numeric fields.
A report should honor your data type. If the field is numeric, you may be out
of luck since 100.1 = 100.10.

 

[Carlee]

hi Duane,

My field type is a 'Text' field. Examples of the data are 100-10, 1000-10...
When i attempt to export the report to an Excel spreadsheet (to retain
groupings and setup), the trailing zeros get truncated. In my environment,
100.10 is different from 100.1.

Any light you can shed would be greatly appreciated.

 

[Duane Hookom]

It isn't clear whether your data values are 100-10 or 100.10. Either way, you
might want to either separate the fields into two separate fields or possibly
change your control sources in the report to add a space to the left ie:
=" " & [Your Field Name]
You might also need to change the control names so they aren't the same as a
field name.