Excel Can´t Calculate.
Aug 6, 2006
HI,
Excel is having difficulties calculating a workbook of mine. It complains about cirkular references, so I had to set the iterations manually.
The workbook holds about 5000 records and I´m using a cosinus function.
The thing is I don´t know what to do. Excel calculates the workbook, but it doesn´t do it right. What setting should I use with the iterations?
I´m thinking of running it in excel 97. Will that help?
I really need to be able to run this workbook. Does anyone have an idea?
Fuga.
View Replies
ADVERTISEMENT
Oct 17, 2014
1. I want to put borders like in excel to my reports in access.
2. I want to calculate gratuity like this: nil for completing less than 1year service, 7 days for service between 1 to 3 years, 14 days for service between 3 to 5 years, 21 days for completing 5 years and 30 days for service exceeding 5 years. The calculations to be made on basic salary and the no. Of days in a month to be taken as 30 days. Can you send me with an example how to do it in a query.
View 1 Replies
View Related
Sep 10, 2007
I searched the archive and didn't find quite what I was looking for, so..
I have an Excel 2003 spreadsheet work-in-progress being used as a template (developed by others) to prepare project cost estimates in a complex regulatory environment. We are 'modelling on the fly' for a number of projects until we are comfortable with the estimate model, after which time I intend to incorporate our 'stable' estimate methodology into Access. Meanwhile, I am 'stuck' with the Excel spreadsheet.
I have a project tracking database (Access 2003), and I want to be able to track my estimates. I do NOT want to embed my spreadsheets into the db, just a filelink. There can be more than 1 estimate per project.
Ideally, the user should be able to define a project in the Access db (or select one already defined) and click a 'make estimate' button, which would generate a new Excel file in a predefined directory (based on the present version of the .xlt file), give it an appropriate filename (based on the Access ProjectID and estimate sequence number for that project if there were others already), open up that workbook in Excel, and then autopopulate some cells based on information showing on the original form in Access!
A separate button for 'Open existing estimate' will eventually be required, but I think I could do that if I can get someone to walk me through the steps required above.
I am somewhat familiar with vba in Access, but am an absolute rookie when it comes to excel.
Edit: I left out that I would also add an appropriate record to a table like tblEstimate which would contain the link(s) to the estimate(s). This table will obviously contain a FK to tblProject
View 1 Replies
View Related
Mar 23, 2006
I have an excel file linked to a table in Access. Several fields are date data types in excel but are showing up as text fields in Access.
My real goal is to do a comparison between two tables, but only if the date of the one piece of data is newer than the other. I had planned on comparing the two date fields but even though I have formatted the date fields in my excel file to be "Date", when I look at the design view of my table it is showing up as "Text" and therefore I am unable to do this comparison.
I'm not sure if it's just something that I'm missing but maybe someone else knows an easy fix to this. I know this is probably a simple question, but I did search the forum and didn't find a thread that specifically dealt with this issue.
Thanks in advance for your help.
View 2 Replies
View Related
Apr 25, 2013
I am using Excel and Access 2010.
I have an excel spreadsheet with 8 tabs. They are all in the same format and column order. They are employees grouped by region. My ultimate goal is to merge all of these onto one excel tab, relatively instantly. I created a master tab and tried doing array formulas and Vlookups, it worked but my spreadsheet was way too slow.
My solution? Import and link them to an Access database, step complete. Create an XML export then import into Excel.
My problem? The only way to update the excel tab with the combined tabs is to save the excel file after changes, go back into Access, re-export to XML, then go back into excel and refresh the data.
My questions, is there any way to automate this process to the point that I can change excel, save, then hit refresh on my excel tab with the XML import to auto-update?
View 7 Replies
View Related
Aug 25, 2013
i want to export a table to excel , open this file and execute a macro from another file.
the code i have now is :
Code:
DoCmd.OpenTable "Overzichtaanwezigheid", acViewNormal
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acTable, "Overzichtaanwezigheid"
Dim XL As Object
Set XL = CreateObject("Excel.Application")
XL.Workbooks.Open ("C:UsersErwinDocumentsOverzichtaanwezigheid.xlsx")
XL.Visible = True
XL.Run "d: est.xlsm!Macro3"
Opening the excel file goes ok, running the macro however not.
View 1 Replies
View Related
Sep 15, 2005
Hello,
sorry to post again my question but just cannot find a solution.
I have a table and would like to copy data into a template in excel. I know that the code will have to make a copy of the template and then copy the data into the new workbook into sheet1.
My table (table1) has 3 fields: SSN, FNAME and LNAME.
I want to copy these fields into cells B1 (for SSN), B2 (for FNAME) and B3 (for LNAME).
I will use a combo to select the recorset to copy.
My problem is how to copy data into the template. I understand that it is necessary to run a copy of the template and then copy the data into the new xls file.
Is there a way I can do this via code? Code help is appreciated. Thank you.
View 5 Replies
View Related
Dec 16, 2004
How would i go about getting the field 'GMargin' (Gross Margin) to be calculated from subtracting 'SellingP' from 'PurchaseP' (Selling and Purchase Prices)?
How do i do this? Help me please :)
View 5 Replies
View Related
Oct 6, 2005
I have a field which I enter a short time such as 3:13, 5:45, etc
Now i am creating a report which at the end of the report I would like to include an unbound text box, which displays the total amount of time for that field (pls note that the total will never exceeds 24 hours) What is the best formula to use in order to calculate this ?
Cheers
View 4 Replies
View Related
Jan 18, 2006
Hello,
I am trying to sum minutes from a database using an Access query, but the format in the database is hh:mm. I know the query to use in Oracle:
ROUND(SUM((substr(actual_time,1,(instr(actual_time ,':' )-1))) *60 + (substr(actual_time,(instr(actual_time,':' )+1),2)))
but not sure of the syntax to use in an Access expression.
Any ideas?:confused:
View 1 Replies
View Related
Oct 21, 2012
how i can export the data from Access to excel using Access VBA for the specified sheet using data linkage with access database. Like we used to do it manually in excel as external data from access.Like we have some codes for linking excel file to database mentioned below;
DoCmd.TransferSpreadsheet acLink, , "region", "F:DB PracticeBook1.xlsx", False, "region"
Can we have something like this to link database table in excel file automatically.So that the excel size won't be that big and also it saves processing time.
View 5 Replies
View Related
Jul 10, 2007
Hello, I didn’t know how to explain this or where to post this but here we go! I have two databases and one of the PC's here at work can’t use some of the items in the database like on one of the databases there is some formulas in text boxes and that PC won’t calculate what’s needed in them and every PC in the plant will except for this one PC. Also the one PC won’t bring up the Allen Browne's Calendar. When you press the start or end button to bring up the calendar it never pops up but if I enter the start date and end dates and use it that way ect then it works. I have no clue why this is just happening to this one PC. Any ideas?
View 2 Replies
View Related
Jan 19, 2006
I want to calculate a data from anthony table, like (20 Jan 2005) + 365 days
will be 20 Jan 2006, how can i do it.
The Table setting is just like below:
Constituent (DataID,...);
Student (DataID, DOB,...);
School (DataID, 4YearsOld(As a Date Format),...)
Anyone can help me, Thank you.:)
View 3 Replies
View Related
May 12, 2007
i have a table with fields "[DATE OF BIRTH]", "age", "temp-age"
i have a query that calculate the age:
example; the query will get user to input the "temp-age" and upon "temp-age" is input and "ok" on the query, it will calculate a person's age and store the value inside table "age".
however, the calculated value for the age have some data problem. for example, when a person whose age is yy:mm 24:05 the age inside the table will be 1924:05. there will be a additional "19" at the age.
what cause the additional "19" problem?
---------------------------------------------------------------------
my query in design view is like this; (see attachment)
it uses " [temp-AGE]-[DATE OF BIRTH] " to calculate the age and store into table "age".
View 1 Replies
View Related
Nov 10, 2006
Hello,
I'm trying to calculate months not days between
EX: 1/1/06 to 6/30/06
I tried to use end date (-) begin date, but is only give me the difference in days. What expression do I use to get 6 months?
:confused:
View 2 Replies
View Related
Nov 19, 2006
Hi, i'm trying to calculate the amount of employee for each company giving me a total in the queries.. how do i go about this? I'm not an person who uses Access to solve this. I have amount 20 Companies stored and over 800's employee.. Here are the field in the table:EmployeeIDEmployeeNameNumberofEmployeeThanks! Hope this helps.
View 1 Replies
View Related
Jan 15, 2007
Hi
I am very new to databases, and think i am a bit over my head. I am doing a database for vacation rentals, and am trying to calculate commissions, basically i wanted to have a set of checkboxes, labeled 10%, 15%, 2.5% and $25, with any combination of these checkboxes being able to be checked.
i want the query to work like this:
If the 10% checkbox is checked i want the query to take the SubTotal for the reservation, and calculate 10% of that number. if the 2.5% checkbox is also checked, i want the query to calculate 2,5% of the reservation and add it to the commissions total (10% + 15%)
I think(hope) that i explained that clearly? Can this be done?
Thanks for your help!
Ben
View 2 Replies
View Related
Mar 16, 2007
What is the easiest way to calcuate a median value for a group of values?
View 4 Replies
View Related
Apr 19, 2007
I am attempting to calculate the Average & Median on a particular field in a query.
=Median("qry2007_KPI_AvgJan_A","ContractSignedToReceived")
query name is qry2007_KPI_AvgJan_A
field name is ContractSignedToReceived
I am getting #Name? error.
Any ideas why? Thanks for any help troubleshooting this.
View 1 Replies
View Related
Mar 22, 2008
In a report, I need to calculate the percentage of clients that respond "Yes" to a question on a survey. I need to create a query that will return the count of the number of "Yes" responses, and the number of total responses.
For example, if 10 clients complete the survey, and seven respond "Yes", I need the 2 fields in the query to be 7 and 10. So far, I have only been able to do this using multiple queries.
Thank you.
View 1 Replies
View Related
Aug 25, 2005
stuck in the middle of a project of employee payroll the following problems are for some suggestions:
if an employee is not taken off in a week and work on sundays, he's paid double OT; if he is not taken off he's not paid; i.e. he can take 4 days off in a month
how can i keep track of this in the db; can i use a table putting 7 days as fields and make it all yes/no so that user can check or uncheck the status against each employee(!); but for whole month how can i calculate
work period start from every month on 25th and ends on 24th in next month.
got tables for attendance and leave
help appreciated
View 1 Replies
View Related
Aug 31, 2005
I cannot figure this out,
I have values that need to be calculated in a field but it will not work with null values, I’m sure this is easy but I have not found a solution.
Thanks in advance.
The attached mdb illustrates my dilemma.
View 1 Replies
View Related
Sep 5, 2006
Hi, can someone help me with this formula.
i have one field called notice_1 this field is a combo boxs multiple choice ( YES - NO )
Then i have 1 field called score_1 ( if notice_1 = "YES" then let score_1 = 5 else then let score_1 = 10 )
Does this make sense , I am an, amateur at access 2003,
Thank You For any help i can get...
John Calcitrai
View 5 Replies
View Related
Dec 20, 2004
Hey Guys.
I have a sub form based on an order table. I had another table which lists products which customers can buy. In the products table I have a field named "units on order" which I'd like to update when an order is made in the form and a field named "units in stock" which upon the user opening the order form again and clicking a button to say that particular order has been delievered, the "units in stock field in the products table will had added the quantity chosen in the order form.
Any suggestions?
Thanks
Tempest
View 14 Replies
View Related
Mar 9, 2005
Hi,
I have a from with 3 fields on it where users can enter numbers of boxes attached to a booking.
Each field stands for a box of a certain type.
E.g.:
20's ==> 1
40's ==> 1
45's ==> 1
Now, I want to calculate a total based on 40 being 1, 20 being 0,5 and 45 being 1,125.
The total in this case would be:
2*20 = 0.5
1*40 = 1
1*45 = 1.125
Total: 2.625
Is this possible, and, if yes, how do I insert this calculation?
Don't know if this is clear enough, if someone has any questions on my goal here, please ask me to clarify...
Thx once more,
Lion85heart
View 4 Replies
View Related
Jan 11, 2015
On this db, the users input a FROM date and a TO date to apply for leave, and the number of days required is calculated. I was able to get this done, but the problem is if they want leave for only half a day. The term that we usually use is FN and AN. What I want is this: If they choose FN to FN, or AN to AN, we should get .5. If they select FN to AN or AN to FN that is one day. Saturdays, Sundays and certain other days are holidays. On Table2 I have a FROM field and a TO field, but no field for AN/FN (yet). How can we accomplish this?
View 11 Replies
View Related
