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 Replies


ADVERTISEMENT

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

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

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

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

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

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

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

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

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

Display Only Positive Values In My Query

Jan 30, 2007

Hi.. I am a beginner using access and there is probably an easy solution for this which i dont know about!

I have made a query where i calculate the tax deductions for a payroll system. my problem is that different workers are to be taxed differently given maritial status and number of kids as well as net income.

I have managed to calculate everything with the kids and wifes but now i need to sort the table after how much they earn to deduct taxes. I have a "first stage NetPay field" now, where i can see the Gross pay minus allowances for wifes and kids.

Now, if that value(first stage netpay) is under 8840 no more deductions are to occur. If the value is between 8840 and 10000 i have to deduct 15% of the excess above 8840

If the value is between 10000 and 20000 i have to deduct a further 174+ 25% of excess over 10000

If the value is above 20000 i have to deduct 2674+ 31% of excess over 20000


So far what i have done is make a new coloumn, where i take "first stage netpay field" and subtract 8840. If the value is negative i thought i could show only the positive values by enterin >0 in the criteria field for the query, this however will make no posts show up..

(I tried entering Abs() in the Field coloumn where i made the expression, and this worked. )


So my question is: is there a function like Abs() i could use that would convert negative values into 0?

Or do any of you guys have a better idea of how to solve this?




Hope someone can help me...


Takstein

View 8 Replies View Related

Queries :: Negative Time Values When Export Access Query To Excel

Jul 10, 2013

The time difference in access query works very fine in 18:00 (Short Time) format , but when i export to excel it came out with "########" and the value is -0.14679132479

How to solve this problem , i keep trying but it doesn't work ....

View 14 Replies View Related

Comparing Values

Oct 28, 2007

i want to know if it is possible to compare values (a string) stored in a field with a field of another table?
for example, i have a table named Courses, it has field Course ID...and Course Requirements.
the field Course requirements can accept only 3 value: A,B and C.
i have used a combo for this.
then, i have Student qualifications table. i have also designed a form to input student qualifications.
my problem is that i have to compare qual. of students with the req of the course, to allocate them a specific course. if both values meet, the db automatically allocates the course to the student.

View 1 Replies View Related

Comparing Values In A Query

Dec 7, 2007

Okay I have a query that pulls from a table. It's pretty simple - nothing big.The query will be used to correct information in some sales tax data.I have 3 colums with sales tax info, and they should all be the same. I want to compare each column to show only those which don't match each other.NetTax, TotalTax, and TaxB are the fields.In the query, for the criteria of NetTax, I put in:<> [TotalTax]When I run the query, I get results, but they are all equal.EG:4.35 4.35 4.35I don't want them to show, and not sure if that's the right way to go about it or what.If I run without that criteria, I get 21,600 results.With that in the criteria, I get 304 results. So it is working somewhat...but for whatever reason some don't seem to be seen as being equal? I don't get it. Data type is number and they are formatted the same way.

View 14 Replies View Related

Comparing Values In A Query

Dec 17, 2007

Hi...
I will try to keep this succinct.

I have a query that totals a column in a sub-form. The query also gets a sum from the main form for the same record. Is it possible to have a message/colour change/warning if the total of the column in the sub-form exceeds the other number in the query? I would need this comparison to take place within this query, rather than creating a new one.

Thanks!
Kathryn

View 8 Replies View Related

Comparing Values From Table1 And Table2

Sep 10, 2006

Hello everyone! I've found much use of this forum recently as I am working on a database concerning my work.

I'm not a newbie working with access but this problem seems confusing at the beginning.

I've got 2 tables
1) Information about produced details. Article, Mass etc.
2) Mass constants
Mass min. Mass max. Constant

What I need, is to build a query that compares the mass of the detail with the range mass min. and mass max. and shows the mass constant that the detail belongs to.

Has anyone tried to solve this kind of issue before?

Greets from Estonia,
K

View 1 Replies View Related

Comparing 2 Tables To Find Duplicate Values

May 9, 2006

Hey all

Im rather new to access but have to use it at work. Ive been asked to see if there is a way to delete duplicate records from a table.

Now, I have 2 tables. The 1st table (tbl_list) contains records of various customer details. This list is old.

The second table (tbl_new_list) contains new customer details.

We have found that we have the same customer details in tbl_new_list that are in the old table.

Im looking for a way to compare these 2 tables so that the values in tbl_list are not in tbl_new_list.

I have tried numerous methods using append queries but nothing seems to work.

Thanks in advance people

P.S the data will be compared through a telephone number.

View 1 Replies View Related

Queries :: Comparing Two Values From Rows In Same Field

Jul 10, 2013

I have a table [VL] with four fields, [vl_id], [product], [vl_date], [valeur_liquidative].

The idea of the table is that you can input and update the value of each product on a given day.

I need to find the difference in days between successive dates (vl_date) each time that a product value (valeur_liquidative) is updated. Values aren't updated everyday as it is not updated during the weekends. I have had a go at this but have struggled..

A picture of what the table looks like is attached for reference.

View 9 Replies View Related

Queries :: Comparing Text Within A String To Keep Only Unique Values

May 15, 2013

I have one field where string contains several words separated by semicolon and my goal is to be able to remove duplicates within the string and keep only unique values. Here is an example:

initial field:
xxx;yyy;ppp;yyy

targeted result:
xxx;yyy;ppp

How this could be achieved ?

View 5 Replies View Related

Random Autonumber Force Positive Only

Apr 1, 2007

I don't know if it's possible, but is there any way of forcing the random autonumber generator in tables to be a positive number only?

The autonumber as I've searched on the forum can be a positive or negative number and I'd like to use the random autonumber feature as a primary key for account numbers for my database, but not using the negative numbers as they will look like odd account numbers with the leading negative.

I could possibly remove the leading negative, but there's then the chance that you'll have duplicates.

Thank you!

View 1 Replies View Related







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