Display negative value as < 1

[wvandenberg]

Hi all,

I have a report displaying data with up to 6 decimal places. The values can be either positive or negative. I would like to display all the negative values with a "<" sign rather than a "-" sign. Here is a sample of the unformatted data

-0.00005
-0.0001
-5
97
-0.0004
8.2
14
-200
36.2
2

Here is a sample of how I would like the formatted data to look

<0.00005
<0.0001
<5
97
<0.0004
8.2
14
<200
36.2
2

In excel, I use the number format General;<General to achieve this format but I have not been able to get anything to work in my Access report. I tried #;<# but then I lose the decimal places.

Any ideas or suggestions on how to get this format are greatly appreciated.

Wendy

 

[MajP]

You could roll your own, (I am sure someone has a shorter version)

Public Function negFormat(varNumber As Variant) As String
  
  If varNumber >= 0 Then
    If Int(varNumber) = varNumber Then
      negFormat = Format(varNumber, "General Number")
    Else
       negFormat = Format(varNumber, "#.######")
    End If
  Else
    If Int(varNumber) = varNumber Then
      negFormat = "<" & Format(varNumber * -1, "General Number")
    Else
      negFormat = "<" & Format(varNumber * -1, "#.######")
    End If
  End If
End Function

test

Public Sub testFormat()
Debug.Print negFormat(123456789)
Debug.Print negFormat(1.23456789)
Debug.Print negFormat(-123456789)
Debug.Print negFormat(-1.23456789)
Debug.Print negFormat(2)
Debug.Print negFormat(36.2)
Debug.Print negFormat(-36.2)
End Sub

output
123456789
1.234568
<123456789
<1.234568
2
36.2
<36.2

 

[wvandenberg]

Thanks, I'll give it a whirl.

 

[zevw]

If I may give my two cents

I would change the controlsource of the text box that displays this value to

=Replace(Str(<field>),"-","<")

This will replace all negatives with the less than sign.

 

[MajP]

Zevw,
The only issue there is the number
-1.23456789
does not come out as
<1.234568
with the requested 6 decimal places.

 

[zevw]

Sorry it took so long!

I would add some more conditioning to it.

IIf([field]<0,Replace(Str(Format([field],"#.000000")),"-","<"),Format([field],"#.000000"))

 

[zevw]

Forget about the whole iif function

Just enter this

=Replace(Format([Sequence],"0.000000"),"-","<")

 

[MajP]

zevw,
Unfortunately that would provide the following output
<0.000050
<0.000100
<5.000000
97.000000
<0.000400
8.200000
14.000000
<200.000000
36.200000
2.000000
not
<0.00005
<0.0001
<5
97
<0.0004
8.2
14
<200
36.2
2

The challenge is to be able to handle more than 6 decimal places
1.23456789 as 1.234568
and an handle an integer
2 as 2

 

[zevw]

Can you please send me the raw data the way it was entered and see what needs to be done.

I am not clear what you need.

 

[wvandenberg]

How should I send the data and what format should it be in (.xls, .txt,etc.)? Directly to you or should I post it on this thread? I've never done this before.

I should also mention that I am performing a calculation on the aformentioned data in another field on the same report and that's why the data needs to be a number (-0.000005) as opposed to text (<0.000005). I was hoping to apply a format rather than actually change the value.

 

[zevw]

send it as a text file

 

[wvandenberg]

I've posted the file to an ftp site for you. Just to explain, the value in column H is a calculation based on columns F and G. The calculation is performed in the Access report and not in the underlying query.

Please let me know if you have any questions or problems.

Wendy

 

[MajP]

Maybe I am missing something here, but I what is wrong with the solution I proposed. The demonstrated code that works and is easy to use. Here is my output.

<.00005
<.0001
<5
97
<.0004
8.2
14
<200
36.2
2

This is exactly as you describe.


It handles integers as integers
It truncates more than 6 decimal places
It provides a < instead of a -

1) Paste the function in a standard module.
2) in the control source of a text box or query:
=negFormat([yourFieldHere])

 

[zevw]

how do I log on to the ftp site?

I need a user name and a password

 

[MajP]

Good point. I forgot to handle null and O. How about

Public Function negFormat(varNumber As Variant) As String
  If varNumber >= 0 Then
    If Int(varNumber) = varNumber Then
      negFormat = Format(varNumber, "General Number")
    Else
       negFormat = Format(varNumber, "#.######")
    End If
  ElseIf varNumber < 0 Then
    If Int(varNumber) = varNumber Then
      negFormat = "<" & Format(varNumber * -1, "General Number")
    Else
      negFormat = "<" & Format(varNumber * -1, "#.######")
    End If
  ElseIf varNumber = 0 Then
    negFormat = "0"
  Else
    negFormat = ""
 End If
End Function

 

[MajP]

I dropped my function into your excel sheet and used it to format a column. I noticed that
0.005 came out to be .005
So modify the code from "#.######" to "0.######"

 

[wvandenberg]

zevw, thanks for your time and suggestions, it's greatly appreciated.

MajP, thanks for your input too. Your solution does work as advertised. It took me a bit to tweak the underlying recordsource for the report so I could implement your solution.

Thanks.

 

[wvandenberg]

MajP,

I missed a couple of your posts as I was working with your original code and this is what I ended up using

Public Function negFormat(varNumber As Variant) As String

    If IsNull(varNumber) Then Exit Function
    
    If varNumber >= 0 Then
        If Int(varNumber) = varNumber Then
            negFormat = Format(varNumber, "General Number")
        Else
            negFormat = Format(varNumber, "0.######")
        End If
    Else
        If Int(varNumber) = varNumber Then
            negFormat = "<" & Format(varNumber * -1, "General Number")
        Else
            negFormat = "<" & Format(varNumber * -1, "0.######")
        End If
    End If
    
End Function

Works like a charm.

Thanks again,
Wendy