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 Replies


ADVERTISEMENT

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

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

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

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

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

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

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

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

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

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

Convert Text To Number In Multivalue Table Field Without Data Loss

Oct 21, 2012

I have a multivalue text field called "Groups" that is generated through a relationship with another table called "Comments". When I set this up I left the field as a text field when it should have been a number field, and now I can't use the database with sharepoint, because the field needs to be a number field.

My Question is there a way to convert it without loosing all my data, as there are over 5000 records?

My Table with the multivalue field is called "Contacts", the field is called "Groups".

I tried update and append queries but I must be doing something wrong....

View 2 Replies View Related

Need Report To Show A Number Of Records Based On A Value In A Field Of A Table

Aug 9, 2006

Hi,

I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description

Data example:
1001, 5, Jackets
1002, 10, shoes
etc

I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets

and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes

If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.

Can anyone help?

View 1 Replies View Related

Convert Text Field To Number Field But Keep Leading Zeros

Oct 3, 2006

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

View 6 Replies View Related

Convert Text Field To Number Field In Query

Nov 18, 2005

As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.

I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.

I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.

Is there any way to get round this?

Many thanks

View 2 Replies View Related

Convert A Text Field To A Number Field In A Query

Feb 28, 2007

Hi,

I have a text field in a table and need to convert it in a query to a number field for sorting purposes, but not have it change in the table.

Thanks,

Jeff

View 3 Replies View Related

Tables :: Update A Field In One Table Based On Status Of A Field In Another Table

Aug 2, 2013

I have started work on a database to track the many (several hundred per annum) projects my company undertakes. A 'main' table lists projects, their fees, their key dates, their project numbers, etc. I have created another table for tasks. Each task is assigned to a project (via the project number), and may be 'open' or 'closed'. I have separated the tasks table from the projects table as there may be several different tasks for each project. It is also good to keep a record of the tasks.

I want to prepare a report from the projects table that lists projects IF they have any open tasks.

In my mind, this means 'If a task, with the corresponding project number, is open, put a 'Yes' in the 'Tasks open' field of the Projects table.'

View 7 Replies View Related

Tables :: Auto Populate Field Based On Answer Of Another Field In Same Table

Feb 28, 2013

Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.

I am not quite sure how to lay this out. I am using Access 2010.

View 8 Replies View Related

Queries :: Can Query Convert A Field Value Into Number Of Records?

May 11, 2013

I build a database to manage some events. To simplify things, let's assume an event takes place on 3 days, from 01-05-2013 to 03-05-2013.

I create a table named EVENTS.

Among other stuff, I create the following fields:
EVENT_first_date
EVENT_last_date

Then I create a query which returns the number of days the event will take place on.

EVENT_total_days: [EVENT_last_date]-[EVENT_first_date]

I use an append query to insert this new record into the table EVENTS_MAIN.

I wish to set the start time and end time for all event days in hours and minutes. I will use these entries for displaying start time and end time for each event days, to calculate daily total hours and event total hours later on.

Therefore, I wish to create a query that will display event date 1, event date 2 and event date 3, based on the number of the EVENT_total_days field, so I can set the start time and end time for each day separately.

Is it possible to "convert" or "switch" the number of the days into number of records? Can a query convert a field value into number of records?

View 9 Replies View Related

Tables :: How To Make Calculated Field Based On Field In Another Table

Jun 18, 2014

Table1 contain Two fields (3Months) and (6Months)

Table2 contain Two fields (3Months) and (6Months)

the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder

View 1 Replies View Related

Tables :: Autopopulation Of Field From Another Table Based On Another Field Criteria

Dec 5, 2012

I have a main table which is autonumbered on unitid, I have another table which is linked via the unitid which holds items purchased for the main table, so the second table is a subform of the suppliers and will have multiple entries of the same unitid. What I would like to do is when an item is entered into the second table, check the main table to see if it exists, if it does populate the unitid field with the unitid from the main table, and if not exist create a new record in the main table with the next unitid number.

This in essence is what I want, bearing in mind that the tblinvoices is a subform and the tblmain is not open.

If ([tblinvoices]![Unit] = [tblmain]![Unit]) Then
[tblinvoices]![UnitID] = [tblinvoices]![UnitID]
End If

View 3 Replies View Related







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