Format number with condition

[Stm]

Hi all,

I'm using Access VBA to run a query and format numeric value according to below and then send out by email.
1) if the value is 0, show a dash.
2) if it is >=1 or <= -1, show it without decimal place
3) if it is (>0 and <1) or (<0 and >-1), show it with 1 d.p. (e.g. 0.51 / -0.49)

I've tried to handle it using Format(value, "#,0;(#,0);""-"" "). However, if the value is 0.5 / -0.5, I wish to display as is, but now it will round up to 1 and display as 1.
I can use if...then...else to handle, but just curious if it can also be handled directly by the same format function?

Thanks.

 

[arnelgp]

if you are using a Form and the form is a Single Form, you can set the format on the current event of the
form (not tested):

Private Sub Form_Current()
Dim fmt As String
fmt="@"
SELECT CASE True
CASE [theTextbox] = 0
    fmt="-"
Case [theTextbox] >=1 Or [theTextbox] <= -1
    fmt= "#"
Case ([theTextbox] > 0 And [theTextbox] < 1) OR ([theTextbox] < 0 And [theTextbox] >= -1)
    fmt = "0.00"
END SELECT
[theTextbox].Format = fmt
End Sub

 

[xavier.batlle]

Instead of using the format property, I'd use the switch command and the format() function in the controlsource property to set the desired numeric format in the query.

 

[CJ_London]

I'm using Access VBA to run a query and format numeric value according to below and then send out by email.

As suggested by xavier, seems to me the switch option is the way to go since this is in a query

Expr1: Format([fld],Switch([fld]=0,"-",[fld]>-1 And [fld]<1,"0.0",True,"#"))

important to get the order of testing correct