Commas in Report and Auto Decimal

[perrymans]

I need commas in the fields of my reports, but I don't want dcimal points if there are no decimal numbers.

However, the only way I know to get commas is to select a format of Standard. But with Standard format and Auto decimal places, it always puts two decimal places.

And I don't want to set decimal places to zero either.

So is the best way to do a custom format? Maybe on load or in the query?

If the number is 127, I want to see '127'.
If the number is 1272, I want to see '1,272'.
If the number is 1272.2, I want to see '1,272.2'.

Thanks. Sean.

 

[THWatson]

Sean
Would either the "Format property" or "Conditional Formatting in a Form or Report" articles in Help solve your problem.

Tom

 

[genomon]

Or look at user-defined formats in help; i.e.
Format(YourField, "#,###.#")

Alcohol and calculus don't mix, so don't drink and derive.

 

[perrymans]

Nothing working so far.

Any other thoughts?

Thanks. Sean.

 

[genomon]

Nothing working so far.

Not so. The example above does work. What is it exactly you are trying to do here? A field in a report? Text box on a form?

Alcohol and calculus don't mix, so don't drink and derive.

 

[TheAceMan1]

How are ya perrymans . . .

In the [blue]On Format[/blue] event of the [blue]Detail Section[/blue], try the following:

[blue]   Dim ctl As Control, idx As Integer, Remainder As Double
   
   Set ctl = Me![purple][b]TextboxName[/b][/purple]
   idx = InStr(1, CStr(ctl), ".")
   If idx > 0 Then Remainder = Val(Right(ctl, Len(ctl) - idx + 1))
      
   If Remainder = 0 Then
      ctl.Format = "#,##0"
   Else
      ctl.Format = "#,##0.0"
   End If
   
   Set ctl = Nothing[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886