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)

1. Convert a positive number to a negative number

2. MS Access Report convert minutes number to HH:MM

3. Convert a number to the typed value of the number

4. Converting csv number to date

5. how to convert week number to date range

6. IS it possible to convert the selected number in a drop down list to negative?

7. Positive to Negative

8. convert long decimal number to 2 decimal places

9. convert number to word

10. Convert Numbers to words for Month

11. Need to Convert DEC number to HEX number to IP ADDRESS

12. Convert number to text in query

13. convert number to time

14. Covert number to autonumber

15. conver number to date

16. how to convert decimal values to octal

17. number to ordinal list (1st, 2nd, 3rd...)

18. Converting negative number to positive using QBE??

19. negative number to positive number

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

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

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

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

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

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!

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

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)

i've put

=MonthNum2Word([Forms]![fmrMonthlyReports]![ComboMonth])

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

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.

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

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

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.

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

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

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.

All the best

Marc

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

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?

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

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