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 RepliesI 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 RelatedWhat is the format to change the negative to a positive when:
=Sum([Field]="Novice")
Text box in report
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??
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!
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 RelatedI'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 RelatedI 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 RelatedI 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?
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
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 .
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?
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?
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.
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
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 RelatedPlease help.
How do i convert a negative value to zero in a Form?
:confused:
How do you change negative number to red in a report's text box? i am thinking it is an iff statement in the control source field but running into issues.
Is there a way to change all Negative nubmers to red in a Report? That is, do i have to change all text boxes individually?
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?
Running Access 2010. I have a large data set here is sample:
Type Capture Date Volume
1 1/5/2014 7
1 1/15/2014 5
1 1/8/2014 3
1 1/10/2014 4
Is there any way to sum the total values for each change in date while keeping chronological order? To get access to produce something like this:
Type Capture Date Volume
1 1/5/2014 7
1 1/8/2014 10
1 1/10/2014 14
1 1/15/2014 19
I have designed many queries in my db and report for every single query. Each report has count filed. =Count(*).
Now I want a report to show these count fields. Which will serve purpose to get overall summary report. I have tried using
[Report]![name of field]
but it does not work.
Could somebody please help?
I have an access report that is grouped by Estimator and for all the records grouped to that estimator, I have totals in the estimator footer. Most of these are straight totals =sum([billings]) etc. etc. but I have 1 total that show only the first record as their total = first([field name]). This is where the problem is. I need to add subtotal for each estimator in the report footer which works fine for the straight total, but not for the field as a first records. I need to subtotal the first record for each estimator.
View 1 Replies View RelatedI have a Report Generated on Access Database, the report is 56 page now i'm tring to put the total records on that report, how do i do that ??? pls help
View 1 Replies View RelatedHow can I generate a report that contains multiple summary total by year. I run the query to give me selected time period(s) for my report. For example: I want to have the summary for the date ranges from the year 1994 to 2001, and then 2002, 2003, 2004 etc. I'm having difficulty for the first summary total which is the date range from 1994 to 2001.
View 3 Replies View RelatedI'm sure this is a no brainer for those in the know - but I dont!
So how do I display the total number of records at the end of a report?
Access 2007
Windows 7
Trying to get a subreport total to show on my main report
getting #error no matter how I code
Running a subreport containing a total in report footer (only field that
name of subrpt total fld subrpt
[SubRptExp].[Report]![gramtrcvd]