Sum Function Adding Incorrectly?
Mar 15, 2006
Hello,
I'm trying to sum values in a table and the result I'm getting is incorrect.
I have a simple table called Test with one row called Number which is Double. I have 4 values in Number field. They are:
-60000
26.6
2661
62849
When I run a query to sum those fields:
SELECT Sum(Number) As Sum FROM Test;
I get the following result:
5536.60000000001
Interestingly if I change the last number in my table from 62849 to 62848 I get this result:
5535.6
The problem seems to be with summing positive and negative numbers together. In my case, if the sum of positive numbers goes over 65535, the resulting sum is incorrect (adding 0.00000000001 to the result).
Does anybody know why this is happening and if there is a way around this? The actual tables that I work with have a large numbers that go well over 65K so this is a problem for me. I couldn't find anything on google about this.
View Replies
ADVERTISEMENT
Mar 1, 2006
Any help would be really appreciated...
I have a database in the database I have products customers orders etc.
In Products table I have productid price etc. I would like to create another table and this table should consist of all product parts for example.
ProductID x uses
metalnumber23
stone45
plasticnumber43
so all metalnumber23, stone45, plasticnumber43 will be in Parts (new table that i will create) when i am entering a productid to Product table for that productid i should be able to choose which parts from parts table that it uses.
so in the order table if i customer ordered productID x I should be able to see all the parts that ProductID needs in the parts Parts table. There will be maybe 2000 parts but ProductID x will use only 3 of them. And if I create a query lets say order from z date to y dates I should be able to see parts needed for those products ordered.
hope you guys understand and give me a head start i would really appreciete
View 2 Replies
View Related
Mar 8, 2008
Hi Guys, sorry to bother you but I'm really stumped. I've looked through some similar sounding old posts on here but they don't seem to answer my problem...
I've got a report, which is grouped, in the footer of each group a SUM label should add the contents of the pricce column, however strangely it only adds the first record of each group as a running total. I've tried both the "Over all" and "Over group" attributes of the running sum property, but they make no difference.
Any help would really save my day. Cheers!
View 11 Replies
View Related
Nov 22, 2006
What is the best way to implement a search function into a switchboard? I need this search function to allow a user to check a database for specific names, so that they do not enter duplicates. Thanks in advance for all advice
View 7 Replies
View Related
Nov 24, 2006
Hi guys and girls,
I'm fairly new to Access and I'd appreciate some help. I've built a form with buttons to open new forms for that specific record (based on queries as all the data is held in a single table). This works fine until I try to add new records. At the moment the only way I can make this work is to add the new record, shut the original form before re-opening it and proceeding on to the other sections (via the buttons I mentioned).
If anyone could tell me how to get Access to save the record before it opens the other forms I'd really appreciate it.
Cheers,
Franny.
View 2 Replies
View Related
Sep 13, 2014
In my DB that we use and a workflow tool, some of our work has and due date and time.If we get the complete_package our work time starts and we have X amount of time to complete our work. This is something i worked on but set it aside, now i am coming back to try and fine tune this so it returns a more accurate value.
So if we receive and [Date_Complete_Package_Received] at 09/13/2014 09:00:00 AM and based on the work being done we have 5 hours to complete the work, then the [Date_and_Time_Complete_Package_DUE] would be 09/13/2014 02:00:00 AM. That part is simple and i have coding that does that just fine. [SLA_Time] is where it gets the amount of time allotted, we have 5,10,12,14,and 16 hours depending on what is being done.
Code:
Me.Date_and_Time_Complete_Package_DUE = DateAdd("h", [SLA_Time], [Date_Complete_Package_Received])
This is done on AfterUpdate of a field on one of my forms and it works the way it is but what i need is to be able to run this through my Workhours Function so i am not getting values that our in off hours.The following doesn't work i know i can't use the Workhours function with the DateAdd but this is just to show what i am trying to do.
Code:
Me.Date_and_Time_Complete_Package_DUE = WorkHours(DateAdd("h", [SLA_Time], [Date_Complete_Package_Received]))
I have a Workhours module and it is used for a lot of things and it works perfectly.
View 1 Replies
View Related
Feb 14, 2008
I have a query
SELECT Sum(TBL_MeasuresInstalled.FundingValue) AS ATP_Value
FROM TBL_MeasuresInstalled
GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis
HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and it returns nothing when there are no records that match the HAVING clause - I want it to return 0 instead
I have searched the forum and people having similar issues have been advised to use the NZ function so I tried
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) ) AS ATP_Value
FROM TBL_MeasuresInstalled
GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis
HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
and
SELECT NZ(Sum(TBL_MeasuresInstalled.FundingValue) , 0) AS ATP_Value
FROM TBL_MeasuresInstalled
GROUP BY TBL_MeasuresInstalled.IDSubmission, TBL_MeasuresInstalled.PriorityBasis
HAVING (((TBL_MeasuresInstalled.IDSubmission)=[Forms]![FRM_SubmissionReport]![cboSubmission]) AND ((TBL_MeasuresInstalled.PriorityBasis)="ATP"));
but I get the same result (nothing returned)
View 10 Replies
View Related
Nov 12, 2013
I have a command button that basically saves the record that i just finished entering. here's the code:
Private Sub cmdAddAnother_Click()
On Error GoTo Err_cmdAddAnother_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Exit_cmdAddAnother_Click:
[Code] ....
What I'd like to do now is add a second function that will make all the controls on the form go to null after the record is saved. so the user can start from scratch and add another record.
I tried adding me.refresh right before "end sub" but that didnt work.
I tried adding "me.controlname.value = null" for every control on the form, and that didnt work either.
I also tried adding this code that i found on bytes.com:
On Error Resume Next
Dim ctl As Control
For Each ctl In Me.Controls
ctl.Value = Null
Next
That didn't work either.
View 4 Replies
View Related
Oct 3, 2006
I have been staring at this for hours. If anyone can see what I have done wrong I would be so grateful.
In query Q_CTPayDeadline_1
Record ID_Tasks = 194
Field [Stage1B] SHOULD (I believe) evaluate to date 2010-01-01, not 2004-03-31, the former being greater than the latter.
The criteria for field Stage1B is
Stage1B: IIF([NzCease]>[PriorEnd],[NzCease],[PriorEnd])
The values of NzCease and PriorEnd are displayed to the right of Stage1B, for quick reference.
As you can see, for recird ID_Tasks = 194, the values shown are
NzCease = 2010-01-01
PriorEnd = 2004-03-31
BUT
Stage1B = 2004-03-31
ie it treats the IIF() function as returning false, when it should be true.
View 3 Replies
View Related
Oct 31, 2007
Hi Folks,
I have created a file sweeping program in Microsoft Access via VBA in a few modules. The program simply reads certain files in certain directories, and determines if, when, and where to copy those files based on certain user inputs.
One of the features I would like to add would be to upload the files that meet that criteria to a conventional FTP server requiring a username and password. However, the last few days of research all tells me that it is not possible to perform FTP functions in Microsoft Access or in VBA.
I am just wondering if that statement is really true? I figure the worse case is that windows has its own version of FTP.exe that you can execute in a DOS window ... would one be able to perhaps create a command line execution via VBA? Any simple ideas or is it not worth the trouble trying to FTP upload some files via Microsoft Access with VBA?
Thank you very much,
Joe
View 1 Replies
View Related
Aug 16, 2006
Hey guys,
I am getting this error "This expression is typed incorrectly, or is too complex to be evaluated"
For this query:
SELECT first([TblProp].[Name]) AS [SName], First([TblProp].[CommentDate]) AS DateCommented, First([TblProp].[No]) AS BNum, First([TblProp].[Indication]) AS Ind, First([TblProp].[PropSubmitted]) AS DateSub, First([TblProp].[Contact]) AS PrimCon, First([TblProp].[Prepared]) AS PrepName, First([TblProp].[Comment]) AS Comment,First([TblProp].[Value]) AS ValueNew, First([TblProp].[Rating]) AS Prob
FROM TblProp
GROUP BY [TblProp].[RFPNo];
This query had been working fine for a good long time, but suddenly it starts throwing up this message. I haven't changed anything at all with this query. Its very puzzling. I went through each of the fields and its the comment field that is causing the problem.
Would anyone have any ideas on why this might be happening?
Thanks for reading this!
Polo
View 6 Replies
View Related
Aug 29, 2006
I have a standard form with header, detail, and footer sections.
The detail section has a range of calculated boxes and the footer section also has a range of calculated control boxes.
There is a button on the form, which runs the following code:
DoCmd.OutputTo acOutputForm, "Divisions", acFormatXLS, "C: ester.xls", False
This works as far as exporting the Detail section of data. However, as soon as it trys to export the footer section, it just appends them onto the end of the rows.
For example, the report lists:
ABCD 10 20
DEFG 20 30
Totals 30 50
The excel spreadsheet after export lists
Controlbox name Controlbox name Controlbox name cbn cbn
ABCD 10 20 30 50
DEFG 20 30 30 50
Is there a way to:
(a) correct this in the export;
(b) format the spreadsheet;
(c) alternative, e.g. drag the data in from a sppreadsheet ?
View 1 Replies
View Related
Oct 17, 2014
I have created several tables, and also created forms associated with each of them. In every case, the primary key is the first column of the table, and it is autonumbered starting from 1. Every table is sorted by that first column.
In most cases, when I bring up the form that is associated with the table in Form View, as I sequence through the records using the buttons at the bottom of the screen, the records sequence in the order of the autonumbered primary key in the order you would expect (1,2,3,etc)
However, on one form, when I do the same thing, the records sequence through in a different order. When it shows "1 of 21" at the bottom, it is displaying record number 8. "2 of 21" displays record number 13, and so on.
I have doublechecked to see that the associated table is actually sorted properly and it is. I've gone to the Property Sheet in Design view, but I don't see anything that would control the sequence order.
View 9 Replies
View Related
Sep 19, 2013
I have created a report from a query. The report contains some numeric fields.
I have set the format of the numeric fields to #,###;#,###;0;0;
Despite this setting, my report is showing numbers as #.###
View 1 Replies
View Related
Jun 19, 2013
Using Access 2007.
I have a table "StoreInfo" that stores the store number, store address, etc as text fields. I then have created a query gathering this data, specifically store number and address as separate fields. When I run the query, the store number and address are correct. When I use that same query in a report, I have one specific store where all data appears on the report correctly except the store number. The store number is "007" but appears on the report as "137". I was able to correct the problem by changing the store number to eliminate the leading 0's.
Since the field is a text field, I just assumed I would not have any sort of problem with the leading 0's. Once I changed the store number to 107, the problem disappeared.
View 3 Replies
View Related
Apr 17, 2013
I have 2 tables one Access, one FoxPro both containing similar information. The idea of my query is to compare both tables and update the Access table if the value in the FoxPro table has changed. This works fine for a number of similar queries that I have. However, intermittently, this query will see two values as different when they are exactly the same and replace the value in the Access table with a seemingly random value. I know the "Random" values are from other records within the table but cannot see any link between the values.
View 2 Replies
View Related
Mar 26, 2015
I have recently started learning to use Access and all is going very well except for the date. As can be seen from the images below I have set up my date function correctly in Control Panel and everywhere I use a date it is displayed correctly. In Access however numbers are added which I cannot figure where they are coming from and don't want them there. In the second image you can see what the various Format options look like. When long format is used in a Form or Report it displays those additional figure. I cannot get 2015 to display but get 15316 or similar instead.
View 2 Replies
View Related
Feb 14, 2008
to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I did a search and it looks like date prompts cause this error sometimes. The message pops up when running from the Switchboard to generate a report. When you hit the button, you are prompted for the Start Date and End Date, but then this message appears.
Running the query directly, not using the Switchboard or Report to generate the data, it runs fine, still using the two prompts.
Any idea on what I should look at to resolve this issue so users can run the report from the switchboard? Let me know if I need to post the sql or something.
Thanks for all replies.
View 3 Replies
View Related
Nov 15, 2007
Hello
I have some data I need to clean.
It is in an address field the majority of the data is fine but there are some records with words that have been spelt with numbers:
2 9RAMBLEGATE
2 Forest V1EW
2 KEM8LE Close
2 BELLEMOT4TE Road
Is it possible using a query to find instances where this has happened so I can flag these?
Any help would be greatly appreciated
Tim
:confused:
View 7 Replies
View Related
Mar 9, 2015
I am using a form with a combo box (FieldExpenses) that lists from a query row source (SELECT statement). Whether entering a new record or editing an existing record on the form, the selection in the combo box populates the field in the underlying table (TableTransactions) with the row ID (ex: 105), not the desired field (ie: Telephone).
I tried using a split form and the table in the form shows the field correctly. If I open the underlying table separately, the field displays the row ID. It's not working as I wish.
View 2 Replies
View Related
Jul 22, 2015
I need to import an excel file with incorrectly formatted date field and it worked only to import them as text:
1 jan 2015
1 feb 2015
1 mar 2015
etc.
Using CVdate converts jan, feb, nov and dec to correct date, but gives an error message with mar to oct.
View 1 Replies
View Related
Sep 24, 2007
I have a functioning query. However, I need to remove the "Term Date" field. When I do that, I receive a message that says "The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." The SQL code is listed below. Any help with this problem is greatly appreciated!
SELECT dbo_v_PenLiab_401k_rpt.EMPLOYID AS EmpID,
QryEligibility.EligDate,
Sum(dbo_v_PenLiab_401k_rpt.[Gross Pay]) AS GrossPay,
IIf((Max([dbo_v_PenLiab_401k_rpt]![Check Date])<[QryEligibility]![EligDate]) Or [QryHrsElig]![EligHrs]=1,0,Sum(IIf([QryEligibility]![EligDate]<[dbo_v_PenLiab_401k_rpt]![Check Date],[dbo_v_PenLiab_401k_rpt]![Gross Pay],0))) AS AdjustedGrossPay,
Sum(dbo_v_PenLiab_401k_rpt.[401k Amt]) AS 401kAmt,
Sum([401k Amt])/Sum([Gross Pay]) AS EEDefPct,
IIf(([QryEligibility]![EligDate]>Max([dbo_v_PenLiab_401k_rpt]![Check Date])) Or [QryHrsElig]![EligHrs]=1,0,IIf([EEDefPct]>0.04,0.02,[EEDefPct]*0.5)) AS ErMatchPct
FROM (dbo_v_PenLiab_401k_rpt LEFT JOIN QryEligibility ON dbo_v_PenLiab_401k_rpt.EMPLOYID = QryEligibility.EMPLOYID) LEFT JOIN QryHrsElig ON dbo_v_PenLiab_401k_rpt.EMPLOYID = QryHrsElig.EmpID
WHERE (((dbo_v_PenLiab_401k_rpt.[Check Date])>=[Forms]![ReportDateRange]![Beginning Date] And (dbo_v_PenLiab_401k_rpt.[Check Date])<=[Forms]![ReportDateRange]![Ending Date]))
GROUP BY dbo_v_PenLiab_401k_rpt.EMPLOYID, QryEligibility.EligDate, QryHrsElig.EligHrs, dbo_v_PenLiab_401k_rpt.[Term Date]
HAVING (dbo_v_PenLiab_401k_rpt.[Term Date]=#1/1/1900#)
ORDER BY dbo_v_PenLiab_401k_rpt.EMPLOYID;
View 2 Replies
View Related
Dec 11, 2013
So basically I need making a function that will count the number of records from another table/query based on a field from the current query.
View 2 Replies
View Related
Jan 30, 2014
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total
-----------------------
2 | 1234 | 80.00
------------------------
1 | 43526 | 20.00
------------------------
> | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer)
Dim SPSelect As String
SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE"
SPSelect = SPSelect & " ItemID = '" & ItemID
SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
=SUM(CalculateSpecialPrice([Item ID], [Form]![FormName]![CustomerID], [Qty Unit]))
#Error
View 2 Replies
View Related
Jun 9, 2005
We have a date function that converts a text date format. Can someone help me with time function to do the same thing? We want military time. The field is like this now: txt fields.
160037
213137
224356
235716
235800
12341
21708
22732
Here is the date function we use:
Function f2Date(strDateOld As String)
Dim strDate As String, strMonth As String, strYear As String
strMonth = Mid(strDateOld, 5, 2)
strDate = Right(strDateOld, 2)
strYear = Left(strDateOld, 4)
f2Date = strMonth & "-" & strDate & "-" & strYear
f2Date = CDate(f2Date)
f2Date = Format(f2Date, "mmmm d yyyy")
End Function
View 9 Replies
View Related
May 25, 2006
Hi all,
I need a little help. In my DB, I have a command button set up (I was tired of typing in dates) for date, but I used the Now function, which also gives me the time.
Now I have over 3000 subrecords of the main ones. I now need to queries transaction for that specific date, but it also retrieves the time.
I tried to go back and change the NOW to DATE in VB, but the code does not run.
How do I change all records that have date and time (using NOW function) and only click that command button to show only the date (mm/dd/yyyy)?
Thanks in advance.
View 1 Replies
View Related
