Rounding Up

Oct 13, 2006

I have the following expression in a query. This gets the order due date takes away todays date then goes through a series of calculations to work out the period in terms of a number of months (Assuming they are all 4 weeks, wrong I know)

((((([Blanket_One]![Due Date]-Date())/7))*5)/20)

My problem is fairly simple I think. I need the output from this expression to always round up to the nearest whole number.

Example (1.0009 would round up to 2)

In excel I can use the function Roundup, is there an equivalent in Access?

Thanks
:confused:
Judo

View Replies

Rounding

Jan 8, 2007

In My Table I Want To Type A Number (4.2) But I Cant Get Access Not To Round That To (4.0) Any Suggestions

View 1 Replies View Related

Rounding

Feb 12, 2008

I am trying to write a query, it needs to multiply 2 numbers from separate columns, then, I need it to Round the value UP to the nearest 0.25? Does anyone know how to write this formula in a query?

View 1 Replies View Related

Rounding

Feb 9, 2005

I am having some problems with rounding numbers. I have a DB using forms and i want the user to enter a weight and it rounds to the nearest half KG, so either ?.5 or ?.0 depending on what the number is. I know i can enter a range check in the table validation or say => 5 AND <= 10 but i am stuck on the rounding validation. I think this must be enterd into the text box of the form but as to what the syntax would be i am lost.

Thanks
Chris

View 3 Replies View Related

Rounding

Jan 8, 2007

I Want To Type 4.2 And Access Is Rounding It To 4.0...ive Tried Changing The Format To General Or Fixed But I Cant Get Access Not To Round

View 3 Replies View Related

Rounding Up

Jul 23, 2005

Have the need to Round up to the next whole. If I have 1.2, I would like it to Round to 2. Is this possible? :

View 6 Replies View Related

Rounding Up

Dec 15, 2005

I want to round up to whole number based on U. the else needs to round up to the nearest cent. i.e. 1.64 rounds up to 1.65.

The first part works but I can figure out how round up the cents in the else part.
IIf([Item type]="U",+1*Int(+1*[Gross]*1.03),([Gross]*1.03)

Any help would be greatly appreciated.

Michael

View 10 Replies View Related

Rounding Up And Down

Jul 29, 2005

I have a form with a field that gives me an average of results. Below the number 10 it works fine - for example, if I have 6.3 as an average it displays 6, if it is 6.7 it displays 7.

If however the number is greater than 10 I have a problem. Should the number contain a decimal it rounds down to 10 or up to 20. ie any number below 14.9 will round down to 10 and any number above 15.1 will round up to 20. Whole numbers display fine.

Any ideas?

View 4 Replies View Related

Rounding

Sep 18, 2007

I need help on writing a function that will cycle thru numbers of a field and allow the user to either round up or down in .5 increments.

For example: (18 is just a random number, it could be any number from 1-200)

18.1 - Round down to 18 or up to 18.5
18.2 - Round down to 18 or up to 18.5
18.3 - Round down to 18 or up to 18.5
18.4 - Round down to 18 or up to 18.5
18.5 - Stays the same
18.6 - Round down to 18.5 or up to 19
18.7 - Round down to 18.5 or up to 19
18.8 - Round down to 18.5 or up to 19
18.9 - Round down to 18.5 or up to 19

Any help would be so much appreciated!

View 11 Replies View Related

Rounding Down To Nearest 1

Sep 16, 2005

I have got a formula in a Query as follows:

Suggested Qty: [StockQty]/[Numerator]

How do I round this down to the nearest 1?

(i.e 7.2 = 7 or 9.9 = 9)

Any help would be greatly received.

Regards

Carly

View 3 Replies View Related

Decimals And Rounding

Oct 27, 2005

I have a field that holds a unit price. I can't use currency as the data type because the currency type is different for each client. I have another field that specifies the currency type for each department. Up to now I've been using a double format with a 2 decimal places. We now need to increase the decimal places to 4 but don't necessarily want to see all four places if only 2 are required - 1.5000 against 1.50. However if I make the decimals as auto, if I have 1.50 it rounds to 1.5.

Any other way to give a bit of flexibility to this?

Thanks.

Dawn

View 8 Replies View Related

Rounding Up Control

Nov 22, 2005

Am sure there is a simple solution to the people that know.....which isn't me!
But I have a text box on a report to calculate % of total and don't know how to Round it up or display the total?

The Text box is:

=([TotalTemp])/([GrandTotal])*100

Tried =Round([TotalTemp])/([GrandTotal])*100 & "%" with no joy and have quite a few of these to implement.

View 2 Replies View Related

Rounding Down To The Nearest 10

Mar 5, 2006

Hi, I am quite new to access, and have the following problem:-

I have a value that is returned in a report of say 17.93, i would like to round this number down to 17, similarly 9.13 should read 0

Can someone help please? Many thanks in advance

:)

View 5 Replies View Related

Help With Rounding Up To Nearest 20

Mar 19, 2007

I have been searching the forum for the last hour or so. I'm new to access, vba and macros. So I asking for some help, please be gentle with me. I'm am trying to round up the the nearest twenty.

example. I have a table that contains an estimate take-off f and I want to round the pipe footage to the nearest 20 for purchase amounts and update the table.

ie.

Item QTY
Pipe 534.08
Pipe 405.5
Pipe 28.83
Pipe 1381

Item QTY
Pipe 540
Pipe 420
Pipe 40
Pipe 1400

Another problem I'm not sure how to fix is, some of the qty totals are two decimals and others are nine. Maybe not an issue but thought it might be.

So if anyone is compelled to help a greenie out it would be much appreciated.

Thanks

View 6 Replies View Related

Rounding Numbers

Jan 8, 2008

I am trying to get Access to round numbers to the third decimal place and I can only get it to round up to the nearest whole number. I have used the help function and tried several things but can not get ACCESS to round as I need it. Any HELP is greatly appreciated!!
Thanks,
DOUG

View 5 Replies View Related

Rounding Currency

Jan 19, 2005

Hoping someone can help. I have looked for an answer and can't quite grasp what I need to do. I am using Access 2000.

Basically, I have a quote form that adds together all job related services and totals them using a currency field. Sales would like to have the option of quoting the job two decimal places or going out to three. So, I need to round to the two different cost options. I can't figure out how to do this. So, my question is:

1) Am I using the correct data type?
2) If currency is most appropriate, how do I show results such as:
$12.327 and $12.33.

If I only change decimal property (therefore, the display) for the fields, the form shows: 12.33 and 12.330. Have tried using the Round function different data types and not having any luck.

Hope this makes some sense. Thanks!

View 1 Replies View Related

Issue With Rounding

Jan 20, 2005

This hopefully is an easy question for you guys :)

My problem is this...I have a lot of code on a command button. One portion is it divides one field by another field. this works correctly; however, it is rounding stuff and I'm unsure what to do about it...here are the examples:

100/10=10
88/10=9
97/10=10

That are the results I am getting when the function is ran...are there some settings on my table that are incorrect? both are set to double / number with decimal places set to auto. Thanks!

View 1 Replies View Related

Problems With Rounding

Jul 29, 2005

Hello,

Am currently working on a basic inventory system, for the most par its going ok, but have come across one strange . I have a field labled VAT to which I want to return the value 17.50 (NOT 17.50 %) but whenever I key it, it automatically changes to 18.00, have tried changing the format but no joy,

Any ideas?
Cheers

View 2 Replies View Related

Rounding Up When I Dont Want It To!

Sep 13, 2005

Hello,

I ve performed a query on a table and now have a set of results which is fine for example �1.56, �1.98, �87.90 (ive set the values in to a currency format), now i want to calculate the total so i have run another query on the last and used 'sum' in the 'total' this calculates the value but rounds it to the nearest integer for example 91!

the thing is i have done this many times before and havent had this problem! i cant understand it, its seriously doing my head in. Ive tried allsorts including the 'round' function which doesnt alter it.

any help would be appreciated

thanks

iso

View 1 Replies View Related

Rounding Numbers

Dec 8, 2006

Hi

Does anyone know how to round a number in the query designer.

Thanks

Dharmesh

This is what i have typed into my field in the query designer. what am i doing wrong.

Base price: Round([base price],1)

View 1 Replies View Related

Rounding Problem

Nov 23, 2007

Hi All, I hope someone can help me with this odd little problem!

I have a query with 2 fields, one called 'list size', which contains 4 digit numbers, and another calculated field called 'list size per 100', which has the simple formula '[list size]1000'. The problem is this field rounds the result down, so for example where the list size is 8979, the list size per 100 should be 8.98, but is giving me 8.00! How can I resolve this? I have tried setting the properties to different things but no luck.

View 3 Replies View Related

Rounding An Average

Jan 15, 2008

I am running a query that returns the minimum, maximum, and average mileage of a list of cars. I have set decimal places to zero in all places I can think of, but of course the average returns with a long decimal output. These figures are then displayed on a report.

How can I truncate the 'average' display so it rounds to show no decimals?

I have tried using Round([YourNumberFieldName],3) in the query but that doesn't seem to work.

The numbers are stored in a table as double numbers. Again the decimal places are set to 0.

Thanks.

View 2 Replies View Related

Rounding Up To The Next Whole Cent

Aug 23, 2005

Hello all,

This is my first thread within this forum.

My company is a service company, (Commercial Heat Treating), for heat treating metal automotive parts. We do not manufacture any products. We already use 2 separate databases for job control. Ie. Entering a new job. Generating process paperwork with processing instructions. Finally, when the order is completed and ready to be returned to our customer, we generate a shipping ticket for that order.

I am creating an Accounts Receivable database that will retrieve customer, part number, PO number, quantity and other selected information from the other 2 databases. I have linked the shipping ticket tables from each of them to the Accounts Receivable database. The invoicing form has a subform that is used to pull the outside data. I use a combo box to select a shipping ticket number. After the shipping ticket number is entered, the other information is populated to the proper fields. I have created 6 additional fields on the subform. Fields 1, 2 & 4 are for data entering. Fields 3, 5 & 6 will be populated with the results. The fields are in order:

(Units) is the multiplier field,
(UnitPrice),
(LineSubTotal) = (Units) * (UnitPrice)
(SurchargePercent) = 2% � 10%
(SurChargeTotal) = Me.LineSubTotal * Me.SurchargePercent / 100
(LineTotal) =nz([LineSubTotal])+nz([SurchargeTotal])

I am trying to total each line on the subform and end up with a grand total for a given record. However, the line totals will not add correctly which in turn gives me an incorrect grand total. The (LineTotal) values are $00.01 low, match exactly or $00.01 higher. Please review this info to see if a simple fix is evident.

The first 2 items perform as they should. All dollar amounts $XXX.0001 and over round to the next whole cent. This is what I want. The grand total code does not add the rounded values.

Private Sub UnitPrice_AfterUpdate()
[LineSubTotal] = Me.Multiplier * Me.UnitPrice

End Sub

[LineSubTotal] rounds correctly = Example 941 x $0.145 = $136.45 (exact = 136.445)
[LineSubTotal] is set to 2 decimal places.

Private Sub SurchargePercent_AfterUpdate()
[SurchargeTotal] = Me.LineSubTotal * Me.SurchargePercent / 100

End Sub
[SurchargeTotal] rounds correctly = Example $136.45 x 4% = $5.46 (exact = 5.458)
[SurchargeTotal] is set to 2 decimal places.

=Sum(Nz([LineSubTotal])+Nz([SurchargeTotal]))
Does not add rounded currency correctly = Example $136.45 + $5.46 = $141.90

The problem is any cent amount that is below .0049 rounds down. I want to force it to round up to the next whole cent.

I have tried codes CLng, CCur, Rnd.

Some more info on my problem. I believe the problem lies within the table. I have formatted the [LineSubTotal], [SurchargeTotal] and [LineTotal] fields to be currency with 2 decimal places. The fields show 2 decimal places until I click on that field. At that point it shows 3 - 4 decimal places. The codes I have written will not take the visible value; they take the true value. How can I force 2 decimal places that will go to the next whole cent? If it is $00.0101, I need to force it to $00.02.

Thanks.

D. Bartelt

View 1 Replies View Related

DateDiff Rounding Down

Feb 28, 2008

Does anyone know if it is possible to round down a datediff functions calculation?

Thanks.

View 6 Replies View Related

Rounding CurrencyTotals

Dec 22, 2006

I have a sum in my section footer and again in my page footer that can be off by 1 penny. I have the format set for currency and decimal places to auto. Is there anything that anyone knows of that I'm doing wrong. I just assume that it is rounding.

Thanks

View 1 Replies View Related

Stopping Number Rounding Up

Apr 2, 2007

G'day All,

I have a table that allows for data to be automatically imported into it or manually entered. When data is automatically entered into the table the number field shows the number as a decimal, but when I manually entered data into the column it rounds it up or down to the nearest whole number.
I have checked the the decimal place setting on the particular item and it is set to allow for 2 decimal places the format is fixed and the number itself is Long integer

Any help would be a appreciated.

View 1 Replies View Related