Convert Positive Number to Negative


Greetings,

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)


Sponsored Links:



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
23:15
But 25 hours and 15 minutes displays as
01:15
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




Hello

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

Ex:

Number: 1
Converted: One

Number: 55
Converted: FiftyFive (or Fifty Five)

Thank you
Terry




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




Hi,

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.

Thanks!

Lucy




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.

Thanks!




hi,
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?

e.g:

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)

	Code:
	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
=MonthNum2Word([Forms]![fmrMonthlyReports]![ComboMonth])

[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.
Thanks




hi,

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

example:

number 65

I want to see: 1:05:00

how can i do this

thanks




Hi,

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.




hi,
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 ,

NOTE" SOME MONTHS ARE 31 DAYS,30 DAYS , 29 ,28
HOW CAN IT BE CONVERTABLE REGARDING TO THE SYSTEM DATE

I THINK AM LOST ........... ANY DIRECTION ???




Hi,

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)

Cheers




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




	Code:
	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

Marc




Hi,

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 question...how 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?

Thanks,

Jennifer