Calculation....need To Drop The Decimals In The Result.
May 2, 2008
Here's what I have so far...and it works fine; however, some results have decimal places; i.e., 2.1666666.
I just need the result to be the whole number. I can't seem to get it to come out that way...I've used "round"...but I must not be putting it in the right part of the formula.
TEST: IIf(Year([EndTerm])>Year(Date()),0,IIf((Year(Date())-Year([EndTerm]))/[Renew]<1,1,(Year(Date())-Year([EndTerm]))/[Renew]))
Thank you for any help!
Carol
View Replies
ADVERTISEMENT
Oct 11, 2011
I'm trying to write a DB to record lab results within a specification limit, and run a report to print the results on a certificate based on an automatically generated batch sample number.
In tables I've managed to get a drop-down list for the 'product type', and another for a list of the tests associated (multiples allowed) with that particular type fluid type. What I'd then like to do is add result/values for each of the tick boxes selected. Currently I just get a list of the tick boxes selected.
Currently I have:
Name (select from other table)
Fluid Type (select fluid 1 to 5 from another table)
Test (test 1, test 2, test 3, test 4... ...test 8) this is where I can select multiples from tick boxes
I'd then like to type a result for (e.g.) test 1, test 4, test 6 or whatever tick box was selected. Is this possible??
I realise a workaround would be to list all the possible tests as columns and just fill in the relevant column.
Better still would be to select a fluid type, and this opens another form with minimum and maximum allowed values, where I could enter the data - can I do this and keep unique IDs for each sample?
This is with access 2010....
View 3 Replies
View Related
Oct 18, 2004
Hi all.
I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA?
Thanks
View 4 Replies
View Related
Dec 4, 2007
Hi ..
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example:
- Commission (calculated by the query) is 100.00
- Special Commission (which is a text box on the form) is 80.00
Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query
Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
View 2 Replies
View Related
May 1, 2007
I have a formula box in my form (textbox) called InventLeft that would calculate product in my inventory. I'd like to make this box flash if the result in it is Less than 2..This way it would catch attention to my user to order more product..Is this possible? Thanks in advance..
View 5 Replies
View Related
Jul 15, 2014
I have a query which works perfectly fine, it's the report that I'm having issues with displaying correctly.
My report is a daily personnel accountability report that shows where everyone is for the day. Instead of having a cumbersome query like I did before, I have opted to just use the results of the selected drop-down option to move the X to the appropriate box of where so-and-so is for the day.
Using
Code:
If [marked_as] = 1 Then
Me.Morning.Value = "X"
ElseIf [marked_as] = 2 Then
Me.Afternoon.Value = "X"
ElseIf [marked_as] = 3 Then
Me.Evenings.Value = "X"
End If
I was unable to get it to work accurately outside of showing the three dummy names under the same column, even though the three dummy names were each placed in one of the three test categories.
I have this set as a private function called when the report loads, which is based on a query that filters down to the exact department or office (depending on the user's selection). Like I said, that part works fine, it's getting the code to accurately display in the correct column.
View 14 Replies
View Related
Apr 1, 2006
Hi there,
I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.
But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?
Thanks
View 3 Replies
View Related
Jul 1, 2004
I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
Would appreciate any help.
View 3 Replies
View Related
Jun 17, 2015
I am creating a driving school database and have four different tables. Student, Instructor, Lesson and Lesson Type.
In the footer of my subform which works out what lesson type the student has taken and from which instructor, I have created a calculation to multiply the number of hours a student does to what type of lesson they take.
=([LengthOfLesson]*[cost]) (This works ok)
I then want to add all of these options together. I have tried:
=sum([OverallTotal]) in the footer (This doesn't seem to work)
and then
=[Booking].[Form]![OverallTotal] to show it in the form
The name of the subform is correct (booking) and name OverallTotal is also correct but I keep getting #error message.
View 3 Replies
View Related
Feb 3, 2015
I am trying to create a image based off a drop down result. I have had no problem with setting up a image based off a static number. e.g 0456432 in the student id field will bring up students photo in the network share.
What I am having an issue with is the control source will look for a number instead of the name that the drop box displays. this is due to a separate table for the drop box.
what is the expression i need to make in order for the name to appear from the dropbox rather than the source id number?
View 3 Replies
View Related
Dec 21, 2013
1. I created a table that contains information about people and their details (mainly numerical info).
2. I created a form containing a command button and a label.
3. I have written a VBA script under the button so that when the button is pressed, the result of the calculation appears as the caption on the label.
My problem is...How do I get the script to run so it does the calculation for every record and places the result as a field in a query.
View 2 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Jul 24, 2012
Is there anyway that access could create a drop down box within a drop down box? For instance, when a selection is made in a drop down box, it opens another drop down box with choices.And is it possible to link an inserted image from a form to open in MSPaint that when it opens, the image is already there?
View 1 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Aug 31, 2005
Hello,I was wondering if anybody knew if it was posslble to show an aproximated value of a field with decimal records. I would like to see only integer values on my form.
View 3 Replies
View Related
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
Feb 23, 2005
I am trying to round decimals to either .5 or 0 right now i have it set to 1 decimal place and it rounds to to the next place. So if a user puts in 1.23 it would round to 1.2 instead of 1.0, is there a simple way to solve this issue?
View 1 Replies
View Related
Oct 18, 2012
Is it possible to return an age as: 21.45 or 64.93?
I have done some searching and can't find any formatting or code to make it work.
I have changed the field format to Fixed and then it gives: 21.00 or 64.00
I like the 2 decimal places for quick at-a-glance-knowing of the age.
Of course I have a formula that returns an age with decimals in EXCEL, that's why I think it should somehow be possible in Access.
View 1 Replies
View Related
Jun 18, 2013
I have a drop box that has four selections from a table (NORTH, EAST,SOUTH or WEST) I also have a another table that has two fields which is a NAME of a person and either NORTH, EAST,SOUTH,WEST depending on the persons name. What I need to happen (within a form) is that when I select NORTH from a drop down box, I can then select names in another drop down box that correspond to NORTH only. Then only names from the South list if I select SOUTH etc
View 2 Replies
View Related
Apr 6, 2006
I have a tblParts that has data in this format 200200.0100 (these are bin numbers). I have created a form with a bound field (field name on the form is BinNo) that goes back to the tblParts. I need to be able for the user to enter on the form the Bin number as it appears above. However at the moment it is dropping my decimal and is only keeping the 200200 portion of the Bin Number. If I enter the bin number correctly into the table it accepts the correct format of 200200.0100. Can someone take a look at the form and the field BinNo to tell me what I may have missed.
Keep in mind that the goal of the database is to enter the bin number into the form and run a query that creates a new table. There is also a delete table button since the data in the form will change for each blueprint document created.
I am attempting to attach a practice db for you to see what I have done but I have not had much success with this in the past. Thanks in advance for your help.
View 3 Replies
View Related
Jul 7, 2005
Hi everybody,
I have to calculate somme totals for decimal numbers, and... I can't make it to work.
I have a report:
Quantity Unit_price Price_without_Taxes Taxes Price
2 1,62 3,24 0,62 3,86
2 2,13 4,26 0,81 5,07
TI: 8,91
TI it's the sum of Price, and here is the problem, because the value of TI should be 8,93 Euro (3,86+5,07) not 8,91. In this case it's a loss of 3 cent , but in other cases, if i have more value to calculate the sum for I can loss a few euro!
The values are placed in the detail section, TI on the footer section of the report. To calculate TI I used a Text box wich format is currency, Decimal places 2, Control source =Sum([Price]).
I make this same calculations on forms also, there the situation it's worst, the value it's rounding down or up in each case, in this particular case insted of 8,93 I got 8,00.
What's wrong with it? I'm making mistakes somwhere?
Any help is apreciated. I need it soooooo bedly! :)
Thanx,
Attila
View 14 Replies
View Related
May 22, 2006
I'm having a problem getting imported data to display what I want. I'm importing a fixed width file and one of my fields is a currency amount. The field value in the file is 12 characters with the last 2 as the cents (000000544990) I have my access field type as currency with 2 decimals (and I've tried many other settings auto,0 etc.) When I do my import it displays my data as $544,990.00 If I tell it no decimals it displays $544,990 It should be displayed as $5,449.90 somehow it keeps adding 2 characters and uses them as the cents instead of using the last 2 from my file.
Any suggestions?
Thanks for helping me out.
Rick
View 9 Replies
View Related
Jan 5, 2007
Hello,
Dont understand what is controlling these decimals. I dont need any decimals for this database. The currency is talking bout money in 1000's and the probability is whole numbers.
Here is where I cant get the decimals to stop:
http://img80.imageshack.us/img80/3685/untitled2pl2.png
http://img77.imageshack.us/img77/6276/untitled3pa6.png
http://img208.imageshack.us/img208/2047/untitled3lk8.png
I have tried different things but no luck...anyone have any suggestions.
Thanks.
View 10 Replies
View Related
Mar 23, 2006
I need to have a field on my form to accept the user keying in the number in this fashion: 200200.1234 but as I currently have it set it accepts only the 200200 portion of this part number. I am a beginner so I am sure this is an easy question for some of the pros out there.
As a footnote the table design has the bin no. as data type - text and I added an input mask of 000000.0000;0; the field on the form is a combo box. Maybe that will make a clear picture for you.
Thanks in advance for your assistance.
View 5 Replies
View Related
Mar 1, 2013
I have a table that I need to enter values into but for some reason when I enter a decimal (ex: .015), it automatically changes to 0. I have the Data Type set as Number, Field Size as Long Integer and Decimal Places to 3. I have another field set the same way and it works fine.
View 2 Replies
View Related
