Stopping Queries From Rounding Off
Dec 19, 2007
I have two tables. Table1 has two monetary value fields (both set with auto decimal places) and table2 has one monetary field. Then I have an append query which appends all data from table1 to table2, except for the monetary fields which are added up and then appended into table2's single monetary field (which also has decimal places set to auto). The formula I use for this is as follows:
Total Value: [Gross Value]+[Adj'mt Value]
Problem is, the total figure appended to table2 is rounded off to the nearest integer, whilst still displaying decimal places (45.00, 201.00 etc). Is there some sort of variable to set in the query to stop it from rounding off the result of this formula?
Any help would be appreciated.
Thanks.
View Replies
ADVERTISEMENT
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
Aug 5, 2013
I have an interesting Access problem. When doing a query, and it does not matter which Access file it is all of them do it, if you tab out or click to another screen, sometimes, the query will stop. I have tried many different network related fixes but no luck. I am thinking the issue is with Access. I have no issue with Excel on Access files.
View 3 Replies
View Related
Aug 22, 2013
I seem to be having some trouble with my unit price. Its not round up correctly.
I currently have this :
Extended Price: CCur(Nz([Quantity]+[QuantityToFollow],0)*[UnitPrice]*(1-[Discount]))
Which comes to £45.74, but the actually price should be £45.72. Its some how rounding up. I think. My current unit price is £7.62 however Access seems to think it is £7.6241.
View 1 Replies
View Related
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
Aug 20, 2013
I have a database that has raw data input to 2 decimal places. There are multiple queries set up to sum the total cost by facility and box type. When the queries are run, the output is to 4 decimal places. I need to round the cost to 2 decimal places. (Right now, it is formatted to show 2 decimal places, but when you click on the number, there can be up to 4 numbers after the decimal.
I have tried to round the numbers within the queries and I get errors asking for parameters. I've also tried to round the numbers in the append queries where I move the final numbers to a summary table. When I do that, I get an error message that the database can't find the table I selected.
I'm not sure how or where it's best to add the rounding function so that my final output is in the format I need.
View 2 Replies
View Related
Oct 10, 2014
I know the Round function exists in MSAccess, but what I need is one that will always round up, and never round down!
Easy enough in Excel, but I can't find one in Access?
View 2 Replies
View Related
Aug 13, 2014
I have a query expression that calculates the number of years between 2 dates using DateDiff. Here is the equation as it is:
BudgetedHQPCalc: Round(DateDiff("yyyy",[DateHired],[DateTerminated]),1)
What I would like this to do, is if the difference is, for example, 1.4, I want the number rounded down, if its 1.6 I want it rounded up, and if its 1.5, i want to use alternate rounding (1.5 down 2, 2.5 up to 3).
Is there an easy way to do this?
View 3 Replies
View Related
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
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
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 23, 2013
I'm trying to get this query to work:
Code:
SELECT Avg(Round([Final 205].[LastOfSumOfRehLOS],2)) AS AvgInpLOS, Avg(Round([LastOfSumOfWaitLOS],1)) AS AvgWaitTime, Count([Final 205].PHN) AS Observations, [Final 205].LastOfIntensity AS Intensity_Level
FROM [Final 205]
GROUP BY [Final 205].LastOfIntensity;
It doesn't found the result to two decimal places.
View 1 Replies
View Related
Jul 20, 2007
In order to prevent inexperienced users corrupting the design of the Forms, Reports etc. of my Access DB, I have unselected the relevant options at Tools – Startup and it doesn’t display the Objects window. However, I find that when I press F12 key, a pop up window appears with ‘Save As’ ‘Save ‘frm……’ To: As …..’ with OK and Cancel buttons. Though a user can save the form with a different name and this does not interfere with the proper functioning of the DB, I find this an irritant pop up window. Is there a way to stop this pop up window?
View 2 Replies
View Related
May 6, 2005
I have a form with a command button which sends a message containing information from fields on the form. This works fine. Problem is, the user can use the "Email Button", when there are fields not filled in. Is there a way to stop the email message from being sent?
View 2 Replies
View Related
Sep 19, 2005
I have 10 fields on a Form. When I load up my Form, I don't want any of them to have the cursor in it.
How do I do that?
View 5 Replies
View Related
Nov 8, 2005
I have a customer who is concerned that their access database may be copied and used by a competitor. I have told him to set up passwrods for all users amd hide the system file which stores the password info so database won't open off site if copied. He doesn't want to do this though. Is there any other way, possibly by encrypting it?
Regards :confused:
View 3 Replies
View Related
Jun 25, 2007
I've got an incrementing invoice number that is just a +1 of the last invoice number. Had a problem today where one user was creating an invoice and took a while to complete the record. As soon as the record was created the invoice number went in as 123 but the record didn't save until she had filled out all the form. In the meantime someone else started to create another invoice and ended up with 123 as well because the previous record hadn't saved. Does anyone have any suggestins how I can stop this from happening? I can't save the record straight away, which would be the obvious answer as they need to fill out quite a few required fields before the record is saved so that they don't have incomplete records.
Thanks
DBL
View 2 Replies
View Related
Oct 17, 2006
I have a booking form database
With a date field.
I want users to be warned when they try to enter a date that already exists in the database in order to avoid double booking:confused: .....
all suggestions appreciated:)
View 4 Replies
View Related
Nov 1, 2004
I have a database that requires new data from an Excel file every month. I have created a linked table, and the the user updates links each month, pointing the table at the new Excel file.
This has been working nicely until today when the Excel file arrived with different column names! The user linked the table as normal and started to run the database, but the queries were looking for different field names so the whole thing crashed. I changed the Excel column names and tried re-linking the table, but all the queries had changed so that the field names were things like "Expr1: [Depreciation_currentmonth]" even though that field name now existed.
Does anyone know of a way of stopping this happening, or restoring the queries without ploughing through each one manually?
Abi
View 2 Replies
View Related
Jan 4, 2005
Quick question, (and hopefully a quick and easy answer).
I have a check box and a combo box. If the check box is not checked and a user clicks on the combo box, I have a message box that pop ups and informs the user that he/she must click the check box first. However, after they ok the message box, my combo box shows it's dropdown list.
Is there a way to stop the dropdown from occurring when they click on the combo box?
Thanks in advance.
View 9 Replies
View Related
Mar 28, 2006
I have a macro which automatically opens a form. I then want the form
to scroll through all the records by itself until it gets to the last
record.
I've got it scrolling through the records, but it doesn't know when to
stop and throws up an error message.
View 4 Replies
View Related
Apr 2, 2006
hi,
can anyone tell me how to stop additions and edits on a particular record in a form, plus the subform that the main form contains,
im trying to put the event behind the change of a text box, shipped date, so when its changed you cant add or edit the record which is the order.
cant anyone point me in the right direction?
cheers
View 1 Replies
View Related
Dec 11, 2006
On my switchboard i've got several forms, whenever i click a link to a new form that form opens in a new window, how do i stop this so that they all open in the same window?
View 2 Replies
View Related
Aug 27, 2013
I am using to select and append data to an output table. Everything is working fine until the last step. The last step appends all the data to an output table from the initial table. The intermediate steps select the data to output.
"AddExpenseToOutput()" is stopping about halfway through the list of criteria. Initially I thought there might be a null in the list, but it doesn't look like there is after dropping everything into Excel to manipulate.
Code:
Option Explicit
Dim rstAccount As DAO.Recordset
Dim rstAccounts As DAO.Recordset
Dim CAccounts As String
Dim Item As String
Dim AccountKey As String
[Code] ....
View 4 Replies
View Related
Dec 2, 2006
Hi again all,
Im wondering how I can stop the messages that pop up when im about to run a update query,
I get the following
1. You are about to run a update query blah blah....
2. You wont be able to undo the changes this action blah blah
3. You are about to update X Rows
Idealy Id love to be able to turn off the messages in just this query, and not globally over the whole database, can this be done?
Also, Id like to get a error if there is one.. so only stop those message that are currently showing...
Any help would be awesome,
Cheers Ezy
View 2 Replies
View Related
Jul 4, 2013
I have a form that contains an autonumber field. Before any data is entered into the form that field displays the word "(Autonumber)".While it does this the subforms on my form that use that same field to run their querys are empty
Code:
Me.AllowAdditions = True
DoCmd.GoToRecord , , acNewRec
[Forms]![InvoiceForm]![CreateNewRecord] = True
Me.AllowAdditions = False
[CreateNewRecord] is just a invisible tickbox that is used to enter some data on the form; I don't want to use it but haven't found another solution.
Anyway. When the user opens the form they have to press this button Unless a new record was previously created and that's confusing. I'd like it in the form load event except I can't "see" the value of the field while it displays "(Autonumber)".My code for the form load is:
Code:
If InvoiceNumber.Value = Null Then
Command82_Click
End If
Where invoicenumber is the autonumber field and command82 is the new record button.
View 11 Replies
View Related
