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 Replies


ADVERTISEMENT

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

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

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

Negative Value Formatting In REPORTS With Calculated Field

Nov 11, 2012

I am completing a report. I've inserted a calculated field in the report. I'm using Access 2010. Here is an example:

=([AccessTotalsOpen]-[AccessTotalsClose])/[AccessTotalsOpen]

I get the correct answer but cannot find a way to Display/FORMAT a negative number with any of the options that I have available to me?.

Any method to format a calculated field in a report?.

I tried this example but could not get it to work. =Format( FIELD ), " 0; (0)";

View 3 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 :: 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

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

Tables :: Showing Negative Numbers In Brackets Without Selecting Currency Format

Jul 7, 2014

Is there a way to show negative numbers in brackets without selecting the Currency format?

I want to show (75,000.00) and not -75,000.00

View 2 Replies View Related

Formatting Numbers

Oct 25, 2006

Hi all,
I am trying to format three fields to write to a table.
For reasons unknow they are created as text so I tried using Cint, Clng and cdbl to export thee fields as numbers so when they are exported to an excel spreadsheet they are as number fields.
This actually works for exactly that but the end result knocks off the leading zero if the value is 0910 it ends up as 910. The source table it pulls this from actually has the leading zero if the length is less then 2.
How do I get to format the fields to numbers but keep the exact value ?

Any help is greatly appreciated.

Mitch.....

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

Formatting Numbers As Addresses

Mar 24, 2008

My database is a maintenance record of our building complex. The unit/building number is the key field. The problem I am having is having access recognizing the unit numbers and building numbers, not as whole numbers.
Example: I-380, 10-380, 2-390, 13-370, and so on. When sorted, access treats them as whole numbers. I have tried two separate fields but it would be nice to have only one field and be sorted by building numbers.
I am not a coder so be gentle.
Can you help?

View 9 Replies View Related

Formatting Subtraction Of Two Numbers

Oct 23, 2014

How can I format the output of subtracting two times as a time value. For instance 10:00 am - 9:00 AM = 1 (where I want to show the output as "1").

View 7 Replies View Related

Queries :: Formatting Numbers To Have A Leading Zero

Apr 24, 2014

I have an INSERT query like so:

Code:

INSERT INTO table1 (column1, column2, column3, column4, column5)
SELECT (field1, field2, field3, field4, field1 & "" && field2)
FROM mastertable

According to the format desired by a client, column5 from table1 needs to have field1 and field2 concatenated and represented as two-digit numbers if they're a one digit (has a leading zero).

View 7 Replies View Related

Queries :: Formatting Whole Numbers And Decimals Differently

Nov 26, 2013

I want to print the results of a query where some values are whole numbers, some have 1 decimal place, some have 0.3333333 recurring etc. Problem is that in order to fit all the columns on a page the column width does not allow all the 0.33333... to display. So what I see is like this (there are lots of columns, I am just giving an example from one column)

891
833.5
######

I don't want to fix decimal places to 1 because then I would see 891.0 instead of 891. How can I format the number field so that whole numbers are shown without decimal place, numbers with 1 decimal place stay like that i.e. 833.5 is fine as it is. But display something like 313.666666666667 as 313.67 rather than ##### getting put in as not enough room for all the decimal numbers.

View 5 Replies View Related

Modules & VBA :: Make Table - Assign Custom Formatting To Numbers

Oct 6, 2014

I am using VBA to execute a 'Make Table' (named 'DT'). One of the fields in DT (named 'Dollars') contains numbers that have 6 to 8 digits; some are positive and some are negative.

These large numbers with no commas (or parentheses when they're negative) are incredibly hard to read.

Any VBA coding that will change both the "Format" and the "Decimal Place" Field Properties on the table I make (i.e. "DT"). I want to the Format property to read (#,##0.00;(#,##0.00)) and the Decimal Place property to read "2"). This will allow me to display a number like -10326786.41 as (10,326,786.41) which is incredibly more readable.

Is it possible to do this programmatically; I've search the internet high and low and could find anything remotely useful.

View 8 Replies View Related

Forms :: Default Formatting Option In Conditional Formatting Dialog?

Aug 1, 2014

Need to confirm whether the Default formatting option in the Conditional formatting Dialog won't work in datasheet view of a form. Please note that all conditional criteria are working fine but not the Default Formatting option - only in the Datasheet view (In Single Form view the default formatting is working fine)

View 11 Replies View Related

Queries :: Search For Multiple Plot Numbers Preferably In One Parameter Prompt With Comma To Separate Numbers

Aug 12, 2014

I'm having multiple problems with my database like things such as -

i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too

I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?

i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:

Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:])
Site - (criteria = Like "*" & [Enter Site:] & "*")
Product - (criteria = Like "*" & [Enter Product:] & "*"

The Query is the one im most concerned about , i can live without a form.

View 14 Replies View Related

Numbers Stored As Text Convert To Numbers?

Jan 10, 2007

Ok so in excel I have some numbers that are stored as text. The reason being that they are zip codes and some begin with 0 and excel doesn't want numbers to start with 0....so when I import these into an access field that has an input mask for zip codes...will it convert these correctly since the field is a text with input mask?

View 1 Replies View Related

Modules & VBA :: Automate Line Numbers And PO Numbers

Aug 24, 2014

I have 2 fields that I would like to automate if possible

One field is called "p/o number" and another field called "line no"

These fields are part of an ordering database

Let say I have 200 items to purchase form 10 suppliers

And form example 20 items from each supplier

What I do at present is put the order number on each line item and the line number

example

p/o number line no

1 1
1 2
1 3

2 1
2 2
2 3
2 4

What I want to do is just put the first po number in the required line . Put the first line number in i.e. "1" and the macro will complete all the p/o numbers and line numbers for me as per the ones marked in red.

Example

1 1
2 2
3 3

2 1
2 2
2 3

View 5 Replies View Related

Sort Negative First

Jan 15, 2007

When i sort values, for some reason the negatives are at the top, followed by the highest positive values. Anyway to turn this off?
Tried google and searching these forums.

View 5 Replies View Related







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