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)

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)

- Convert a positive number to a negative number
- MS Access Report convert minutes number to HH:MM
- Convert a number to the typed value of the number
- Converting csv number to date
- How to convert week number to date range
- IS it possible to convert the selected number in a drop down list to negative?
- Positive to Negative
- Convert long decimal number to 2 decimal places
- Convert number to word
- Convert Numbers to words for Month
- Need to Convert DEC number to HEX number to IP ADDRESS
- Convert number to text in query
- Convert number to time
- Covert number to autonumber
- Conver number to date
- How to convert decimal values to octal
- Number to ordinal list (1st, 2nd, 3rd...)
- Converting negative number to positive using QBE??
- Negative number to positive number
- Help on converting Yes/No field to positive number
- Change a negative number to a positive
- Convert numbers to words WITH negatives
- Convert number to fixed length text field
- Converting Positive to Negative

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

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

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.

Thanks!

Lucy

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 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!

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

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)

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 FunctionI 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.

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

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

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 ,

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

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

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 FunctionHope it's useful for someone.!

All the best

Marc

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?

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

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

Matt

Code:

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)

Else

MyNumber = ""

End If

Count = Count + 1

Loop

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))

Else

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

thanks

thanks

Not finding an answer? Try a Google search.