Separating Positive And Negative Numbers

Jan 24, 2008

I have a field on my report that contains positive and negative numbers. I'm trying to find a way to Sum the column twice for two different fields (one for positive sum and the other negative) To make this easy let me give a small example with explanation of the issue I am having. 14 rows of data containing positive and negative numbers in a field called [Rooms]:

1) -1
2) 1
3) 1
4) 1
5) 1
6) 1
7) 1
8) 1
9) 1
10) 5
11) 1
12) -5
13) 1
14) 1

The positive numbers represent confirmations, the negative numbers represent cancellations. I need 2 fields to separate these. The first field's control source is Sum([Rooms]). No problem there. Access does the math and comes up with "8" confirmations which is correct. Then in the second (Cancellation field) I try something like Sum([Rooms]<0) to get the negative numbers which should add up to -7 (-1+-1+-5), but instead I get -3 because it's not summing the values, it's really returning the number of rows that has a negative number. Is there any way this can be done at the ControlSource Property of a report field? Or if I could do a "Where" clause in a ControlSource I could acheive it another way, but I don't that is possible. Is this just something I'm missing with the way I'm stucturing the ControlSource or is it not possible. Or alternatively, is there a way to separate it in the query and have a separate query field for positive and negative going into the report. I can do one or the other in the query but I can't seem to do both without showing values of each type in both query fields.

Thanks for your help!

View Replies


ADVERTISEMENT

Making Negative Numbers Positive

Feb 4, 2006

I have a query where I have some negative numbers (f.e. -403,26) which I like to make a positive (i.e. 403,26).

How do I do this??

View 3 Replies View Related

Reports :: Negative Numbers Show As Positive

Aug 28, 2013

I am trying to have a number that is negative hold its negative value but show as positive when it is displayed on a report.

View 6 Replies View Related

Query For 1 Or 0 In One Field According To Positive Or Negative Numbers In Another Field

Jan 13, 2015

I need to create a query in Microsoft Access to enter 1 or 0 in a field based on whether numbers in another field is positive or negative. Here are the details.

1 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a positive number
0 entered in a "late" field if "TotalHoursAheadOrBehindScedule" field has a negative number

View 2 Replies View Related

Positive And Negative Amounts

Dec 20, 2005

I need to make a coupel of statements that display the total (sum) of negative amounts and positive amounts from a single list of results which comtains both negative and positive values. The two answers need to be displayed in the footer of a report.

View 12 Replies View Related

Sum Produces Negative Number - I Want Positive

Jul 2, 2005

What is the format to change the negative to a positive when:

=Sum([Field]="Novice")

Text box in report

View 5 Replies View Related

Compare Negative Values With Positive

Jun 5, 2013

I am trying to to find a way to show all the positive values with nagative, in order words, if the value is 10,000 is should be followed by corresponding -10,000 so that the user could investigate if the entry is a reversible GL.

View 2 Replies View Related

Comparing Positive To Negative Values

Jan 20, 2014

I'm doing a bank-rec database, so on the one hand I'm comparing positive debits, to negative credits. How do I query to get, say, "Bank1 -$1000, Bank2 $1000"

View 1 Replies View Related

Queries :: Sum Positive And Negative Values In Same Field

Jun 18, 2015

I am trying to get sum "+" and "-" Values in same Field. I created new Expression Builder in Query and i wrote there :

A: Sum([tablename]<0)

of course it did not work.

How shall i update my expression to work this thing?

View 8 Replies View Related

How To Change Negative Total To A Positive One In Report

May 30, 2013

I have a report which is counting and totalling numbers, but in the numbers show as negatives - I need them to show as positives.I managed to change the total in one counting field to positive but I've forgotten how - or where I put the code....

View 8 Replies View Related

Queries :: Convert String To Positive / Negative Number

Feb 24, 2015

I have an expression that converts a string to positive/negative number. It seems to be working fine, however, it doesn't convert "00000000001}" to -10. It shows as 10.

This is the formula that I am using in my query:

PJUNAccrual: IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",-1,1)*(Val([PJ_ACCRUAL_ADJ_X])/IIf(IsNumeric([PJ_ACCRUAL_ADJ_X]),100,10)+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "A" And "I",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-64)/100,0))+(IIf(Right([PJ_ACCRUAL_ADJ_X],1) Between "J" And "S",(Asc(Right([PJ_ACCRUAL_ADJ_X],1))-73)/100,0)))*100

View 1 Replies View Related

Queries :: Purchase Order - Negative To Positive Number

Aug 14, 2014

This query tell me what quantity I am short of to make a part

diffqty: [StockQty]+[OnOrderQty]+[PendingQty]-Sum([OrderQty]*[ProductQty])

This will return a negative quanity iE -200

what I then would do is copy that to a purchase order but remove the negative sign can this can the formula be changed in anyway to give the positive number .

View 3 Replies View Related

How To Make Negative And Positive Values Affect Queries Differently

Jul 10, 2014

I have created an Access 2010 Database for tracking inventory. As parts move to each location, quantities automatically subtract from the previous location. I now realized that some parts are destroyed.

The problem is the if I simply enter a negative value to indicated that a part was destroyed at one location, it appears as a positive value in the previous location. Is there a way to make negative values not affect a specific query?

View 4 Replies View Related

Forms :: Textbox With Exclamation Mark If Value Is Negative Or Actual Number If Positive

Nov 5, 2014

Is it possible to format a textbox such that a negative value doesn't show numerically but in some other form (like a cross or exclamation mark from the Wingdings font, for example?)

I have a couple of textboxes which take their values from a function. The function is designed to return a long integer value which should always be greater than or equal to zero (the function counts the number of outstanding e-mails in a given Outlook folder, hence it should always be zero or higher)

However, as part of my error handling, if the function can't connect to the Outlook folder for whatever reason (for example, if the user does not have the appropriate permissions, or they do not have it mapped etc.), then it returns -1 (so I have some sort of value to identify a fail)

So currently, if such an error occurs, my textbox(es) will show -1 (and I can use conditional formatting to highlight this)

But it would be nice if it showed an "X" or "!" instead, and only showed the numerical value for 0 or positive. Is this possible?

View 14 Replies View Related

Tables :: Convert Positive Number To Negative Based On Another Field In A Table

Jun 1, 2013

I am trying to create a table for income and expense [catergory] and would like the amount being entered into the [amount] field to have a negative or positive value on entering based on the category chosen.

so in my table I have

[catergory] which is chosen from a look up table ( which is either an INCOME or EXPENSE )
[amount] which is entered in the next field ( which has to return a positive or negative value based on the catergory choosen upon entering the data)

I would like to go one step further and indicate this negative value in red is possible in my FORM.

View 3 Replies View Related

Separating Numbers & Text Into 2 Columns

Dec 29, 2005

Hi!!

I have a quick question. I have a field that has information that looks like the below. I need it to be in 2 separate columns, but there is no space between the numbers and text otherwise I would know how to do this in excel. Anyone know how to do this is excel or access?

11000031377A & A AMERICAN DETECTIVE BUREAU


It should be separated as....

11000031377 A & A AMERICAN DETECTIVE BUREAU

Any and all help would be appreciated!!! Thanks!

View 2 Replies View Related

Convert Positive Number To Negative Number

Nov 22, 2011

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.

View 2 Replies View Related

Changing Negative Numbers To Zero

Jan 3, 2005

Howdy Folks,
I have an equation that will often return negative numbers in an update query.
I need to be able to change any negative to zero, and update the table with that zero.

So far, all I've been able to do is ignore negatives with only positive numbers updating.


Thanks

View 1 Replies View Related

Importing Negative Numbers

Jan 23, 2008

Good morning,

We have some programmers creating text files from our mainframe. We will then import these text files into Microsoft Access. We have some negative numbers in the text files that the programmers have formatted this way for example, $100.00-. When I import the file into Access, it ignores the negative sign. I told our Lead Analyst that the programmers should format the negative numbers this way, -$100.00 in order for it to import correctly. She doesn't believe me and thinks I'm not importing the file correctly.

Does anyone have an literature on this so that I can show her so that we can move forward? Or can anyone confirm that what I'm saying is correct, it needs to be formatted with the negative sign in front of the number, not behind it.

Thank you

View 1 Replies View Related

Modules & VBA :: Using Between And Negative Numbers

Sep 15, 2013

Code:
If Me.Text250 = BETWEEN -15 And 15 Then
Me.Label253.Visible = False
Else
Me.Label253.Visible = True

End If

Even when text250 =8 label253 is visible.

View 8 Replies View Related

Formatting Negative Numbers

Apr 19, 2011

I try to modify an access 2010 template from the MS website.The problem is that the template as is programmed does not accept as input negative prices. I need to use negative prices for offering discounts on my invoices. In my country the discounts are only displayed as products with negative prices/values. We do not use % discounts.

i try to create an products with negative price and to use it as an discount on the invoice. Both the products input form and the invoice input form give me the same error : "please specify an price list that is not negative" or "please specify non zero positive prices" ( sometimes it allow me to enter but when the software try to validate the form/invoice i just entered it give me those error/messages)

I changed all the field from currency in general numbers Where do I have to change in order to remove those formatting/condition..? The template has a lot of macros.. I an not able to change those macros if there are some formatting conditions in them. If it is allowed I will post the link to the free template i use to be more conclusive..Or I can give/ upload the template I use.
the link to my data base ( dropbox)

View 3 Replies View Related

Modules & VBA :: Negative Numbers In Calculations

Nov 18, 2014

Ok i know negative numbers can be a bugger in calculations:

Code:
Function FEV1pred(height As Double, ClientID As Double) As Double

Dim a As Double
Dim Age As Integer
Dim DOB As Date
Dim TestDate As Date
Age = DateDiff("yyyy", DOB, TestDate) + CInt(Format(DOB, "mmdd") > Format(TestDate, "mmdd"))
'In this case the clients Age is 39
a = -0.0244

'Now for the fun stuff...

FEV1pred = a
'returns -0.0244 so i know the dim a holds the neg number
FEV1pred = (a * Age)
'returns 1.708 (which is incorrect, how this calculation is reached.)
FEV1pred = (a * 39)
'returns -0.9516 (which is correct)
End Function

So something about the dimming of Age seems to be doing something to the equation but i can't work out what?

View 1 Replies View Related

Queries :: Rounding Negative Numbers Up To 0

Mar 13, 2015

I can not have any negitive numbers show up in my field so I need to round up and negatives to "0" ...

View 10 Replies View Related

Yes/No Fields Bring Back Negative Numbers

Mar 20, 2007

I created a database for a survey. There are a lot of number fields and a few yes/no fields. I did a query to give an average for the number fields and a sum of the yes for the yes/no fields. I get the right number for the yes/no fields, but the number is negative. What do I need to do so that the number is positive?

Thanks

Robyn

View 7 Replies View Related

Negative Numbers In Query Are Not Sortin Correctly.

Sep 26, 2007

I have a table with data that is similar to this:
HCP1 DATE (Fields)
-.1 7/19/2007
-.2 6/14/2007
0 9/9/2007
1.2 4/23/2006
-.3 4/4/2007
2.4 3/7/2007
0 2/5/2006
1.1 2/6/2007

Etc. There are 0's, negative numbers and postive numbers. Some with at least one decimal point. I am trying to sort the data by the highest number in HCP1 field. When I run the query in Access it looks great. But when run through ASP on the server it is only giving me the negative numbers, no 0's no positve numbers. It would return -.1 in this sample data as the first record.

Anyone know what might be going wrong on the server side of things. The query statement looks the same on both sides. (server and access)

Thanks for any help.

View 5 Replies View Related

Reports :: Preventing Negative Numbers In Report

Mar 7, 2014

I have a report where I have equipment tested every 90 days. I have a field called TEST DATE formatted using the medium date format

(07-Mar-14). I have another field called RETEST DATE where my query adds 90 days and displays the Retest date as (07-Jun-14).

I have a unbound box with a heading called "Day(s) passed retest date.

In the Control Source I am using =Datediff("d"[Retest Date],Now()). In this scenario the result is -90.

Is there a way for this -90 to stay at 0 until the 91st day where it will count up from there?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved