Change the decimal value of a field based on text value of another field?

[RandV]

Let's say I have a plain text field for for unit of measurement, and a number field with 3 decimal places for quantity. All my reports have been built around a kilogram measurement, with space in the reports allocated for say 7 digits, 4 the left of the decimal and 3 on the right. Now on the database end I want to convert some of these existing kilograms into grams.

In my reports the quantity field in the Details section is already a mathematical equation: =([Field1]*[Field2])/[Field3]. Is there a way in the report designer to to add on top of this to change the field format?

if unit.value = "GRM" then
   quantity.decimalplaces = 0
else
   quantity.decimalplaces = 3
End if

In my ad hoc experience with Access reports anything in the details field can't be taken into VB for editing. Basically I want to verify if this is the case or if there is actually away to do this. I know I could set the decimal place to auto, go back to the program logic that prepares the tables the report runs on and hard code the the decimal places in or out, but that's going to be a whole lot of back work.

[pbaldy]

Code like that should work in the detail format event, provided you're using print or preview.

[RandV]

Code like that should work in the detail format event, provided you're using print or preview.

Okay I'll have to give it another try then... I'm sure I've tried it before and was left with the impression that you couldn't. Thanks

[pbaldy]

No problem; I tested it so it should work.