Forms :: Currency Rounding For Retail Dollars

Dec 14, 2013

I need to create a SIMPLE solution for Retail rounding. Below are is an Example of Detail lines in a DataSheet Form that represents the price for an Item.

QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$14.0049 = 1 * $10.53 * 1.33

I need the price to end with a Currency Decimal of $$$$$.x9 for all prices. The example above rounds to $14.00 for with 2 digit rounding or if I use 3 digit rounding $14.01.

I need it to be rounded with a .x9 either $13.99 or $14.09 or to the nearest .x9.

Subsequently, every Detail line we calculate in the Datasheet needs to end with an .x9 for Retail Pricing.

There are thousands of different UnitRates (costs), and the Pct (Percent of Markup) can also change.

Additionally, I sum the Detail Lines and the Totals need to match. One of the Sum Fields is Calculated as

=Sum(IIf([taxable]=True,Nz([QuantityXUnitRate]),0))

This ends up Totaling to $28.01 which is wrong for 3 Digit rounding of the Detail Line, because $14.01+ $14.01 = $28.02.

I also tried Rounding

=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),0),0))

And after that I still got the wrong amount $28.00.

I tried this with 2 instead of 0 with no difference

=Sum(IIf([Taxable]=False,Nz(Round([QuantityXUnitRate]),2),2)),

I know one of 2 refers to the Nz and the other to Rounding, but I not sure which is which at this point.

If the last significant digit need to be .09 or .19 or .29 or .39 or .49 or.59 or.69 or.79 or.89 or.99 then everything should Sum up correctly or I hope.

Here is another Example
QuantityXUnitRate = Quantity * (UnitRate*Pct)
$14.0049 = 1 * $10.53 * 1.33
$22.2642 = 1 * $16.74 * 1.33

However, I get the correct Detail Line of $14.01 and $22.26
and using =Sum(IIf([taxable]=False,Nz([QuantityXUnitRate]),0)) same as above, I get the correct amount $36.27.

How to make a with a SIMPLE solution where all Decimals end up with a .x9 and then I'll tackle the Sum if necessary.

View Replies


ADVERTISEMENT

Currency Control Won't Show Dollars

Oct 17, 2005

I have a form with various controls that reflect dollar amounts.

One of those controls, despite deleting and replacing it, despite deleting the field whose value it displays, despite changing it to 'general number', saving, then changing back, despite comparing its properties with that of numerous normal-behaving controls, refuses to show a dollar sign, nor zero cents values. I've compressed and repaired in between deleting fields and controls, too, to no avail.

Otherwise it is working correctly in that calculations based on it work fine and it shows the number of dollars it's supposed to. Just no dollar sign and refuses to show zero cents...

Can anyone suggest to me what's up with this thing?

View 4 Replies View Related

General :: Desktop Services Template Changing Currency To Pounds From Dollars

Nov 13, 2013

How would I go about doing this?

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

Beginner-Tutorial For Retail/inventory Table(s) Set Up

May 5, 2007

Access Guru's:

I am a beginner at best with Access. (I'm using Access 2003) I have a small in home business as a second job making silk flower arrangements. I am now to the point that I need to track my inventory, quantity used, COGS, etc. Most of my arrangements are in gift shops on consignment.

In lieu of buying Quickbooks Manufacturing ($$$), I want to start tracking everything with Access so I can at least get some nice reports at tax time.

The problem I am having is I cannot translate the tutorials that Microsoft supplies to this application, they only cover setting up basic tables. (ie: in a retail/inventory application - do I set up 1 table, 1 table & subtable, 2 tables????)

I set up a table so far with 4 basic inventory fields:
Item description (record includes: flower, vase, foam, etc) / Color / Qty on Hand / Cost. Then (on the same table) finished goods fields of: Qty Used / Arrangement # / Qty Left / Sold for. While trying to figure out how I am going to track how much each arrangement cost, I'm not sure this set up will work.

I probably went into too much detail for my question, but I wanted to give some kind of background. Is there a CD/Book/Tutorial that specifically guides one through setting up a retail/inventory table?

Sorry so wordy.

Catherine

View 1 Replies View Related

Tables :: How To Change Default Currency In Access 2013 To A Foreign Currency

Dec 20, 2012

how to change default currency in Access 2013 to a "foreign currency" (i.e. to Indonesia Rupiahs) without changing control panel (Region/Language>currency) - now setup as USA ($US)?

View 3 Replies View Related

Summing By Name With Dollars Amounts

Jan 10, 2005

Heelo all:

I have a form based on a query. This form has a combobox with 4 names in it. When a person is selected form the combo box, other fields automatically populate IE: room number, address, etc. Another field is a currency box which also populates automatically.

This is what I wish to happen: When I select a person from the combo box, can it show the TOTAL amount received for THAT PERSON in a separate text box?

Any help is most welcome.

Regrds,

Dee

View 7 Replies View Related

Queries :: CountIF - How Many Plans A Retail Chain Has Over Its Stores

Dec 2, 2013

I am trying to count how many plans a retail chain has over its stores.

So
WeekNo | StoreNo | Product |PlanNo
1 | 10001 | Dog | A
1 | 10001 | Cat | B
1 | 20008 | Dog | B
1 | 30005 | Dog | B

Query/report to show
Product | PlanNo A | PlanNo B
Dog | 1 | 2
Cat | 0 | 1

Numbers in Query/report are a count of Stores selling the product

One of the issues is that the data is duplicated over the weeks, I'm thinking to use max on week number so It only looks at the last available data. And how to write the query.

Currently I'm doing this
PlanA: Sum(IIf([PlanNo]="A",1,0)
PlanB: Sum(IIf([PlanNo]="B",1,0)
.
.
.

But it looks at the whole of the table for all the weeks...

View 14 Replies View Related

Forms :: Decimals In Number Fields Automatically Rounding?

Aug 5, 2013

I have created a sub form in a form that allows data entry for order details. I have a column that holds order amounts that automatically rounds up or down, and I don't want this. I have examined the back end table properties for this particular field, and the number field is set to general.

View 4 Replies View Related

Forms :: Preventing Decimals From Rounding In Form Display

Mar 26, 2013

I have created an Access 2007 database that uses a form for data entry. While the data is stored on the table exactly as entered, the data entry form rounds up and only displays the whole number.

In the properties of that form field, I have the format as fixed, with decimal places as set to 1 and is visible. However, the number still rounds. What do I need to do differently to make the number display as entered on my data entry form?

View 1 Replies View Related

Forms :: Add / Change Currency Symbol

Jun 3, 2014

I need to have the pound sterling symbol ( £ ) in a field, but only have two options 1- Dollars ( $ ) and 2 - Euros .

I have done a search but cant seem to find out.

How can I get pounds or change one of the others.

View 3 Replies View Related

Forms :: Dots Or Commas In Currency Field

Nov 5, 2013

I have a form where I combine several fields in 1 textbox. At the end there is an amount. It should look like below:

The total cost of the products is EUR 29.342,45

I have the following formula in the textbox:

Code : =" The total cost of the products " & [Currency] & " " & [TotalPrice]

It would be great if the TotalPrice could be with the dots and coma in the correct way.

I tried to format it, but it was giving me nothing, the numbers stayed the same.

View 7 Replies View Related

Forms :: Convert Currency (Or Number) Into Words

Feb 19, 2015

Is it possible to convert a number (whether in the "Standard" or "Currency" format) into words ?

View 5 Replies View Related

Forms :: Unbound Calculated Field - Show Results As Currency

Oct 16, 2014

I have an unbound calculated field [Txt1] that I would like to show the result as currency. The fields in the calculation are:

[CR] number, currency
[Commission1] general number, two decimal places
[Commission2] general number, two decimal places

The calculation is: CR15W * (1 - Commission1) * (1 - Commission2)
If I just set the field to currency it works fine. But I want the field to show the following:
"Some Text" & " " & Format(Calculation, "Currency")

When I tried to include the format it to currency I get type mismatch, run time error 13.

So I thought that perhaps I should try the following just to see where my error starts and I get the same error message when I use:

Dim Val1 As String
Val1 = CR15W * (1 - Commission1) * (1 - Commission2)
Txt1 = Format(Val1, "Currency")

Everything I have read says the formula is:

Format([Calculation], "Currency")

Is correct. So why won't it work for me?

View 8 Replies View Related

Forms :: Conditional Formatting Of Currency Field In Entry Form

May 26, 2015

I have some Conditional Formatting of a Currency Field in an Entry Form. The cell is conditionally formatted with a yellow background when the value is zero.

I now wish to add an additional condition to the expression such that even if the value is zero it will not be formatted if a specific check box is TRUE.

I have tried modifying the expression such that it reads;

[currency field]=0 AND [check box]<>TRUE.

It correctly turns off the conditional formatting if the check box is ticked, but does not turn it on again when the check box is unticked.

View 14 Replies View Related

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

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 5 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







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