Calculated Field - Rounding To .99
Jun 25, 2015
So I have a Calculated Field
If the price is .01 to .08 I want it to round up to .99 if it is 1.00 to round down to .99
The current Calculated Field is this:
Code:
IIf([ORIG_LOCATION]="",0,IIf([ORIG_LOCATION]="DL",Round([SHIPPED_PRICE]*1.667,2),
IIf([ORIG_LOCATION]="PJ",Round([SHIPPED_PRICE]*1.25,2),
IIf([ORIG_LOCATION]="SB",Round([SHIPPED_PRICE]*1.667,2),
IIf([ORIG_LOCATION]="WF",Round([SHIPPED_PRICE]*1.25,2),
IIf([ORIG_LOCATION]="CNC",Round([SHIPPED_PRICE]*1.667,2),
IIf([ORIG_LOCATION]="CNB",Round([SHIPPED_PRICE]*1.25,2),
IIf([ORIG_LOCATION]="WFS",Round([SHIPPED_PRICE]*1.25,2),0))))))))
View Replies
ADVERTISEMENT
Mar 24, 2014
I am using access 2010. I have a query with an iif statement on a calculated field im trying to round up. ex.
Code:
TotalDiscount: Round(iif([field1]="Piece",[int]-[decr],[field2]*[field3]/[field4],3))
I get an error something like "wrong number of arguments or expression on comma.
View 2 Replies
View Related
Aug 4, 2015
I have a very basic query with the following calculated field in it:
ProjRevNRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentNRC]*[Rev Flow Through],0)
I went into the properties if that field inside the query design and changed it to Standard with 0 decimal places and it worked fine.
But when I based a crosstab query off the query that contained the above calculated field, I cannot seem to get the numbers to format correctly. 1231313.424 is shown instead 1,231,313 and I don't have a line in the Properties window to even change the decimal places. It doesn't recognize when I change the format to Standard. I have tried using Round([ProjRevNRC]) which gets rid of the decimal places as desired but does not show commas.
I'm sure it's a simple part of the Round expression that I am missing but nothing has worked.
View 4 Replies
View Related
Apr 17, 2013
I have a field that sums pounds. I need to convert this to tons (rounded to the nearest 3 decimals), then multiplied by $67.50. This will calculate a fee payment.
This is what I have now:
Code:
=Round(Sum([Hazardous_Waste]/2000*67.5),3)
The total pounds is 2675.
After dividing by 2000, Access generates a number of 1.3375. It rounds 1.3375 to 1.337 which generates an incorrect final total. Not sure how to alter this to round properly.
View 9 Replies
View Related
Jan 22, 2013
Is there anyway to turn off the rounding function in calculated fields - I need to divide a currency amount by a whole number to calculate the number of full coin bags that will be needed (bags contain different totals according to the coin value) - a whole number.
Then the result is used to calculate how much loose coin will remain.
But regardless of the field type and/or the format, the number rounds when I reduce the decimal places to zero. I need the result to be the whole number and to calculate as the whole number.
View 4 Replies
View Related
May 15, 2006
I have got an expression to calculate the number of weeks between the charging start date and the dispatch date - wanting the system to round up the answer to the full week eg if start 1/Jan and dispatch 14/Jan then system correctly returns 2 weeks, if dispatch date is 15/Jan then I want it to return 3 weeks but it still returns 2 weeks and same right up to say 20/Jan difference. Is there any simple round up function similar to excel, search has proved fruitless.
No of weeks: DateDiff("ww",[charging period start],[dispatch date])
Any help appreciated thanks IW -- Cleveland. UK
View 1 Replies
View Related
May 27, 2014
I am creating a student database for our instructors to track attendance and grades. The grades will be printed out as a progress report / student transcript. In order to comply with our accrediting agencies, I must calculate the units of study credits (two separate columns) and have the total Credits round down to the .5
So for example: column 1 = .40 , column 2 = .30, Total = .70 but the rounded down total would be .50
This is the Excel formula used to get that calculation =FLOOR((E11+G11),0.5) How do I create the expression to do the same in my report in that field?
View 4 Replies
View Related
May 31, 2013
Im building an expression where I am dividing fields by constants and adding them together. I need all answers always round down, so 19/20=0, 21/20=1
View 8 Replies
View Related
Nov 11, 2013
I have a field in a table that keeps rounding the data entry off. It has identical properties to the field next to it but one rounds and the other does not. I tried deleting the field and creating a new one.
View 5 Replies
View Related
Nov 8, 2012
For some reason I cannot enter decimal values without the number rounding up or down. I've already set it to 'Number' and 'double' and have also tried 'Fixed' set to 2 decimal places. Right now the only way it works is if I set it to 'Text'. However I need to do some calculations with these values eventually.
What's strange is I have another table in the same database that is NOT rounding and is working just fine.
View 3 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
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
Jul 23, 2015
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
View 3 Replies
View Related
Jun 10, 2015
I use access 2010.
I am assuming by the error code, one can not use a calculated field to calculate another field.
View 1 Replies
View Related
Feb 9, 2006
Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]
There are 2 tables:
tblPartsList
PartNum
PartDesc
tblPartsListCategories
OrderPrefix
OrderNumCategory
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
View 10 Replies
View Related
Apr 20, 2006
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 2 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Mant Thanks
View 3 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
View 1 Replies
View Related
Aug 11, 2005
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
View 12 Replies
View Related
Apr 2, 2006
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
View 1 Replies
View Related
Apr 20, 2006
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 1 Replies
View Related
Oct 26, 2006
i have opened a field (which was created in a table) in a form as a calculated fieled
the calculations are ok but only in the for
the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
** here is a sample file
View 2 Replies
View Related
Oct 15, 2007
I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
View 14 Replies
View Related
Dec 5, 2006
Hello,
Can someone please help with this expression?
I have a table with text field Cust_ID and a
Number field MCounter.
I would like to put in an expression as source in a form
Text field based on the same table to AGGREGATE
all MCounter values for a Given Cust_ID
Something like
Sum(MCounter) where "Cust_ID"=[Cust_ID]
Is this a correct expression?
Many thanks for any help.
View 3 Replies
View Related
Feb 22, 2007
This seems a daft question but I have searched the forums and can't find an answer so I hope someone can help me.
I have a table: Register(RegID, Pupil, Activity, Duration, Preparation)
I have a query that I want to calculate the total hours the pupil has spent on activities.
The Duration and Preparation fileds are held as number of minutes so for a 1 hour event the number 60 is held.
I have a query with 4 fields:
Pupil
SumofDuration
SumofPreparation
TotalHours = ([SumofDuration]+[SumofPreparation])60
The problem I have is that TotalHours is calculating as an whole number.
e.g. I have a Record
Pupil = 701
SumofDuration = 270
SumofPreparation = 0
TotalHours = 4
But I want TotalHours = 4.5
I have set the Duration and Preparation field sizes to Decimal and Format to General Number.
I don't know what else to try.
View 4 Replies
View Related
Mar 10, 2005
Can anyone tell me why this won't work? It calculates the 2nd argument (reduction_man) but not the first (reduction)
I have tried both of these and still can't get it to work.
=IIf([reduction])>0,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))
=IIf(IsNull([reduction])=False,([reduction]*[2004volume])/(13-[cutinmonthnmbr]),([reduction_man]*[2004volume])/(13-[cutinmonthnmbr]))
Thanks!
View 1 Replies
View Related
