0

MS Access has a "Format" setting for each field. For a numeric field, one of the options is "Percentage". When percentage is selected, the behaviour of the number of decimals changes. For example, with 0 decimals specified, the field will actually store 2 decimals. So, for example, a stored value of 0.05 is displayed as 5%.

The problem is that when retrieving column information via GetSchema, the precision for that example is 0 (actually NULL). This is a problem since the GetSchema information is what I'm using to ensure the user doesn't enter more decimals than can be stored.

Any ideas how to fix this?

ic3b3rg
  • 14,629
  • 4
  • 30
  • 53
  • Why not change the column to store 2 decimals? That's what a percentage is. This is not a fault of the percentage format setting. – HK1 May 04 '12 at 14:24
  • You're missing the point. I want to handle any field that the user has defined in the database. I don't want to have to tell the user how to define their database. – ic3b3rg May 04 '12 at 14:37
  • You're very right - I did miss the point. I was thinking "textbox" when you said field. I would be inclined to think that the feature you're programming is not compatible with table/field based formatting, at least not in this case. I have a standard practice of storing data in it's most complete and raw format and then I handle all formatting in Queries or in the GUI. I think it's bad practice to put formatting in the actual tables/fields. – HK1 May 04 '12 at 15:48
  • I agree with you there. It's an option that Access provides. I was hoping to be able to deal with it programmatically. – ic3b3rg May 04 '12 at 18:31
  • Does GetSchema allow you to access the formatting property in the table? – HK1 May 04 '12 at 18:37
  • You're probably trying to avoid this but I think you could use DAO to check the properties of the table. See this: http://visualbasic.about.com/od/usevb6/l/aa101602a.htm – HK1 May 04 '12 at 18:40
  • Thanks for the info on DAO but, like you thought, I'm trying to stay away from it. GetSchema doesn't provide any info on the formatting. I also discovered that the problem is not what I initially thought. I changed the field to "General" formatting and 2 decimals... Access still reports a scale of 0. Then, within Access (with those settings) I edited a row and entered a value of 0.00015 - it accepted it. Unbelievable. – ic3b3rg May 04 '12 at 19:02
  • Welcome to the world of M$. They brag about all their compatibility and interoperability but these kinds of problems are all too common. – HK1 May 04 '12 at 19:09
  • yup, just par for the course I guess. Thanks for the help! – ic3b3rg May 04 '12 at 19:16
  • @ic3b3rg: I do not know the answer to your question. Just wanted to mention Formatting (e.g. General, Percentage etc.) is a formatting only (for display purposes). It does not limit the data that can be entered. Field type and size are what matter. So, when Access accepted 0.00015, it was absolutely fine. As long as the field size and type (e.g. double) allow it. – Igor Turman May 04 '12 at 21:26

0 Answers0