Limit Decimal To Two In Text Field

[Rob]

I am using the DateDiff function to find the minutes an activity takes.
Since the DateDiff function returns a Variant I am storing it as a Text filed
in the data base. In a report I am deviding the value by 60 to derive the
hours but am consistantly generating numbers with up to 5 decimal numbers. I
would like to limit the decimal numbers to two in the text field but am
having no luck. Any suggestions other than using a number field?

 

[Allen Browne]

Use the Format function to generate a number with 2 decimals, e.g.:
Format(DateDiff("n", Date, Now()) / 60, "#0.00")

DateDiff() returns a variant because it's the only VBA data type that can be
Null. A Number field can contains a Null, so I can't see any justification
for using a Text field. My suggestion would be to use a Long Integer to
store whole minutes.

 

[Rob]

Whoops!! My mistake. I am now saving the DateDiff value in the DB as a
number. Still, I am not able to control the number in my reportl. The value
shows up as 3648.01666666667 instead of 3648.02 even though I have the
control set to general number with the decimal places property set to 2. Any
advice would be appreciated.

 

[Allen Browne]

Good: Number is better.

Use Round() before you assign the value to the field, e.g.:
Round( 9999 / 60, 2)