Convert Positive Number to Negative


Searched the forum for a method to convert a Positive Number to a Negative Number based on the data in another field. Could not find anything.

I get data in Excel 97 which contains a field called DetailAmount. All of the numbers are positive.

Another field called TransactionType contains letters. The letter P indicates that the number in the DetailAmount field is a Negative number.

Q. Is there an expression in Access or Excel which will convert a Positive Number to a Negative Number so that the calculations will be accurate?

Thanks !!
(Using Office 97 at work)

Post your answer or comment

comments powered by Disqus
This has to be a simple way to convert a positive number to a negative number. For example, convert 5 to -5. The field is quanitity and I wanted to convert to negative number. I tried to use negative = "-" & [quantity] but it doesn't work. Can someone help? Thanks a million!

This is for a MS Access Report .
I'm trying to convert minutes number to HH:MM
User has 75 minutes for sumofduration
In the report I have
=Format([SUMOFDURATION]/(24*60),"Short Time")
and this works fine,
BUT, now any time the number of hours go over 24, the sum starts over at 1.
For example: 23 hours and 15 minutes displays as
But 25 hours and 15 minutes displays as
If I use this method I need to make sure that the HH continue to increment past 24 so that I can display 25:15, 101:20, 1001:40, etc.
Thank you in advance for your reply


Is there a way in Access 2003 to convert a number to the typed value for the number?


Number: 1
Converted: One

Number: 55
Converted: FiftyFive (or Fifty Five)

Thank you

I am working on a query which is based on a table which was populated by a comma deliminted file. One of the fields is a 6 digit number(calculated date field) which I need to convert to a six digit date. Is there a function which will convert that number to a date. I have tried various functions with the expression builder, but no success. The number is 104114 and should convert to an April date in 2004. Thanks for any input.

Dick White
Colorado Springs, CO


Is there any a simple way to convert week number to date range. For example, if I have the data=3, then I would like to convert it to 1/11/2009 to 1/17/2009.



Hi all...

On occasion users might need to add a negative value to a form. Currently they have a drop down list of numbers, is there anyway to either add a tick box or button which will convert that number to a negative value?

Hope I make sense, thanks

I am a novice database builder only because I had to become one for work. So please bare with me in my lack of knowledge. I'm sure someone has run into this before so I'm hoping someone can help me out.

I am trying to convert a currency field from a positive number to a negative number, but the determinate on if it needs to change to a negative is based on the entry in another field. I have a field called DocBal and another field called DocType. If the DocType is "AD" then the DocBal value needs to be negative. If the DocType is anything else then the DocBal needs to stay positive.

I've tried an IIF statement, but it isn't working.

Any help would be greatly appreciated.


I have dividing one int field by another and the results varies from 0 to 9999.999999999999

When I add a SUM(newnumber) into a group field in reports I get the error :
This expression is typed incorrectly, or it is too complex to be evaluated.

I think it might have something to do with the large decimal place.

from my query can I convert this number to 9999.99 at most ?
ie, only 2 decimal places..
Access should then beable to add the values,, correct ?

thank you for any help..

i have a field inside a table. the data in the field is in number, how can i convert the number to word inside a report?


1 (data inside a table field) convert to "Jan" upon display in a (report textbox)
: :
: :
12 (data inside a table field) convert to "Dec" upon display in a (report textbox)

I am trying to convert Month numbers to words
eg. 1 -> January, 4 -> April

but i cant get it working....

I've looked at some examples involving converting currency into words but havnt got me anywhere

here's my code for the module MonthNum2Word(int)

	Option Compare Database

Option Explicit
Public Static Function MonthNum2Word(ByVal month_int As Integer) As String
Dim month_word As String

Select Case (month_int)
Case 1:
    month_word = "January"
Case 2:
    month_word = "Febuary"
Case 3:
    month_word = "March"
Case 4:
    month_word = "April"
Case 5:
    month_word = "May"
Case 6:
    month_word = "June"
Case 7:
    month_word = "July"
Case 8:
    month_word = "August"
Case 9:
    month_word = "September"
Case 10:
    month_word = "October"
Case 11:
    month_word = "November"
Case 12:
    month_word = "December"
End Select

'Return Month in words
MonthNum2Word = month_word

End Function

I am trying to call this in a text label where in its control value
i've put

[Forms]![fmrMonthlyReports]![ComboMonth] is a combo box that allows me to select the month for a query.

Does anyone have code that will convert a DEC number to and IP Address?

I want to have a question in my query that does not get answered by date but by a concatenated field named "MonthYearPeriod".

We always forget how many days in a month. But we remember the month and year. So for say a Sales Range of Nov 2006 to Oct 2007, we prefer to respond to the criteria question as: 11-2006 and then 10-2007.

FiscalMonth and FiscalYear are both Long Integer number fields in the table.

Query field concatenated:
MonthYearPeriod: [FiscalMonth] & "-" & [FiscalYear]

criteria line:
Between [Month-Year START DATE] And [Month-Year END DATE]

However, i get other crazy time periods. So this is not working. Maybe on the Query field box, I need extra coding to convert the number to text?

I assume that a Dash sign between the month and year are fine.


i have a field with numbers
how can ii convert the numbers to a total time


number 65

I want to see: 1:05:00

how can i do this



I have a table and one of the field, I wish to convert from number to autonumber but I have many data inside this database, do you think it is possible.

Pls see enclosed example (Actually there are many data, but i just key in a few. To tell you this because I might worry that you may ask me to delete the whole field and re-do since the data is only a few)

Please advise.

i have this main three fields

date of buying "date/time" formated
duration "number" formated
maturity"date/time" formated

doing this formula,
Maturity = [date of buying]+[duration]

where duration is in months
how can i convert the number to date/time format , so i can make the formula works,
now it adding only days ,




does anyone know how to convert decimal numbers to octal using a comand in a query , for intance in excel the command is DEC2OCT(val)


Hi All,
Here's a quick and nasty function to convert a number to it's ordinal representation.
1 = 1st
2 = 2nd
3 = 3rd

	Public  Function IntToOrdinalString(MyNumber As Integer) As String
    Dim sOutput As String
    Dim iUnit As Integer
    iUnit = MyNumber Mod 10
    sOutput = ""
    Select Case MyNumber
        Case Is < 0
            sOutput = ""
        Case 10 To 19
            sOutput = "th"
        Case Else
            Select Case iUnit
                Case 0 'Zeroth only has a meaning when counts start with zero, which happens in a mathematical or computer
science context.
                    sOutput = "th"
                Case 1
                    sOutput = "st"
                Case 2
                    sOutput = "nd"
                Case 3
                    sOutput = "rd"
                Case 4 To 9
                    sOutput = "th"
            End Select
    End Select
    IntToOrdinalString = CStr(MyNumber) & sOutput
End Function

Hope it's useful for someone.!

All the best



Im sure its possible I just cant fond the syntex. I have a table with positive numbers, negative numbers and zero's in. What I am trying to do is get a query to drop the 0's and convert the negative numbers to positive - any ideas please?

I have a calculator form in Access that I am trying to create a +/- command button in. This should allow the user to switch a number from negative to positive and from positive to negative. Here is the code that I have written.

Private Sub NegativeOrPositive_Click()

If txtReadOut.Value > 0 Then txtReadOut.Value = "-" & Me!txtReadOut.Value
If txtReadOut.Value < 0 Then txtReadOut.Value = " " & Me!txtReadOut.Value

End Sub

changing the number from positive to negative works beautifully, but when I try to switch it from a positive to a negative it stays a negative number. I know that is because of the second If statement. The value that it is using for the Me!txtReadOut.Value field is already a negative number so all it does is put a space infront of the negative number and displays that. Here is my do I get the second statement to not use Me!txtReadOut.Value as a negative number? Is there different code that should be used for this all together?



I have a table with Yes/No fields. When I run my query it calculates the yes/no fields up to give a percentage. It shows it as a negative number. How do you format it for a positive number.

You will have to forgive me, I have actually asked this before but have lost the answer.

I was given a very simple solution.

I have a number of negative numbers that I wish to change to positive.

Somethng must have changed to this forum since my last visit on 25th November 2005 as cannot access any of my old threads where I could have looked for the previous answer.

Thank you

I have tailored some code on the internet to make it work to my needs. I have read that it is fairly simple to tailor the code to make it handle negatives. Could someone help me out? I am having trouble making it work. Here is code that works perfectly, but only for positive numbers:
Function SpellNumber(ByVal MyNumber)
    Dim point, percent, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion "
    ' String representation of amount
    MyNumber = Trim(Str(MyNumber))
    ' Position of decimal place 0 if none
    DecimalPlace = InStr(MyNumber, ".")
    'Convert percent and set MyNumber to dollar amount
    If DecimalPlace > 0 Then
        percent = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
        MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber  ""
       Temp = GetHundreds(Right(MyNumber, 3))
       If Temp  "" Then point = Temp & Place(Count) & point
          If Len(MyNumber) > 3 Then
             MyNumber = Left(MyNumber, Len(MyNumber) - 3)
            MyNumber = ""
        End If
        Count = Count + 1
    Select Case point
        Case ""
            point = "zero point"
        Case "One"
            point = "one percent"
        Case Else
            point = point & "point"
    End Select
    Select Case percent
        Case ""
            percent = " zero percent"
        Case "One"
            percent = " one percent"
        Case Else
            percent = " " & percent & "percent"
    End Select
    SpellNumber = point & percent
End Function
' Converts a number from 100-999 into text *
Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3)
    'Convert the hundreds place
    If Mid(MyNumber, 1, 1)  "0" Then
        Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    'Convert the tens and ones place
    If Mid(MyNumber, 2, 1)  "0" Then
        Result = Result & GetTens(Mid(MyNumber, 2))
        Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
End Function
' Converts a number from 10 to 99 into text. *
Function GetTens(TensText)
    Dim Result As String
    Result = ""           'null out the temporary function value
    If Val(Left(TensText, 1)) = 1 Then   ' If value between 10-19
        Select Case Val(TensText)
            Case 10: Result = "ten "
            Case 11: Result = "eleven "
            Case 12: Result = "twelve "
            Case 13: Result = "thirteen "
            Case 14: Result = "fourteen "
            Case 15: Result = "fifteen "
            Case 16: Result = "sixteen "
            Case 17: Result = "seventeen "
            Case 18: Result = "eighteen "
            Case 19: Result = "nineteen "
            Case Else
        End Select
      Else                                 ' If value between 20-99
        Select Case Val(Left(TensText, 1))
            Case 2: Result = "twenty "
            Case 3: Result = "thirty "
            Case 4: Result = "forty "
            Case 5: Result = "fifty "
            Case 6: Result = "sixty "
            Case 7: Result = "seventy "
            Case 8: Result = "eighty "
            Case 9: Result = "ninety "
            Case Else
        End Select
         Result = Result & GetDigit _
            (Right(TensText, 1))  'Retrieve ones place
      End If
      GetTens = Result
   End Function
' Converts a number from 1 to 9 into text. *
Function GetDigit(Digit)
    Select Case Val(Digit)
        Case 1: GetDigit = "one "
        Case 2: GetDigit = "two "
        Case 3: GetDigit = "three "
        Case 4: GetDigit = "four "
        Case 5: GetDigit = "five "
        Case 6: GetDigit = "six "
        Case 7: GetDigit = "seven "
        Case 8: GetDigit = "eight "
        Case 9: GetDigit = "nine "
        Case Else: GetDigit = ""
    End Select
End Function 

Thanks in advance

I imported an Excel sheet with a field defined as number/numeric. In my make table query I need to convert that field to a fixed 9 position field with leading spaces.
I cannot concatenate the field with a set number of leading spaces because the input field is not always th same size.
I have tried using different attributes of the Format function but no luck.
Any help or information is greatly appreciated.

Hi - what property do I need to set in order for a pos number to be changed to a neg # when entered. Example: User enters 10, once they tab off that field, it auto changes to a neg value.

Not finding an answer? Try a Google search.