I am trying to use the Excel worksheet function "Forecast" to calculate from existing data in my database. I have successfully gotten the data points into arrays, but when I try to call the forecast function I keep getting the following error: "Unable to get the Forecast property of the WorksheetFunction class". I have imported the object library. This is on a company computer and I do not have write privileges to the C: drive. The database resides on a network drive.
Am I facing some sort of security issue?
Public Function xlForeCast() As Double
Dim MyHeight As Variant 'Will be the point for which you are forecasting, in this case height
Dim MyRange() As Variant 'Will be the independent element of the forecast function
Dim MyRange1() As Variant 'Will be the dependent element of the forecast function
Dim MyArray() As Variant 'Temp array to hold the query result set values before being split into the two preceding arrays
Dim db As DAO.Database
I need to clean up data from text file which is huge.
I wonder if Access Expression Builder can mirroring "IF function" from Excel Here is what i am trying to do The data consist of multiple customer and multiple date. But the layout only specified customer ID once eg.
CustID: aaaa Date 01012013 02012013 03012013 CustID: bbbb 01012013 02012013
When exported the file I used Fixed Width command to separate Date Column and Cust ID column.
Below is the result that I am looking for CustID: aaaa : aaaa Date : aaaa 01012013 : aaaa 02012013 : aaaa 03012013 : aaaa CustID: bbbb : bbbb 01012013 : bbbb 02012013 : bbbb
1. Column A is Date
2. Column B is Customer ID
because Customer ID in column B only appear once, I need to create another column to populate that Customer ID whenever the transaction related with that Customer
3. Column C is the column where i tried to populate Customer ID to each date related to that Customer.
If I worked in excel the formula will be --> IF(AND(C5="",B6=""),"",IF(AND(C5="",B6<>""),B6,IF( B6<>"",B6,C5)))but in access i am stucked.
I am drawing a blank on how to proceed with this requirement and was hoping someone out there could help me.
I am creating a graph that shows all of the orders we have taken, shipped, and what we have bid on. There is a table that holds the customer, no of units ordered, no of boxes per unit, number of units to ship each week and start date.
I need a formula that will calculate all of the week ending dates until the no of units ordered is 0.
For example, Capital ordered 100 units, 12 boxes per unit, 2 units to ship per week and shipments are scheduled to start on 2/19.
This information is diplayed on a graph. The graph shows all of our open bids and based on shipments per week, displays a forecast of what's coming up.
Hello, I'm totally new to this forum. I have a database with the vehicle information in one table. The Vehicles' Odometer reading stored weekly in another table. I have the vehicles' service history with the Odometer reading of the last service in another table. The first two tables are linked by parent, child relationship. I now want to forecast the approximate vehicle service due date based on this information. All I want to do is extract the last 10 Odometer Readings from the Weekly Odometer Readings Table. Find the Average Kilometers with those 10 values. Come up with a figure. Determine the last value entered in the Weekly Odometer reading along with the date. Pull the last service history for the vehicle, with the odometer reading and date. Compare these 2 readings to see if the vehicle is due for service, and if so based on the average Kilometers run every week, determine the approximate date the vehicle is service for due. I badly need help on this one. So if someone could help me on this please let me know. Thanks
I need to create a production forecast form based on previous sales history.The history is based a sales and grouped by month & year
So on the form, which needs to be a continuous form, I want products to show as rows and months as columns The sales history per month needs to be displayed as well as a field allowing to user to enter the production forecast.
I can write the sales history to a temp table.However I never know how many months history the user is going to want displayed at run time. Could be 3, 7, 12 or 15!
Attached spreadsheet shows what I am trying to achieve. Is this possible and if yes, how would I do it?
I am stuck with a simple fact. Nz() function works in Access queries. When I try to import data via these queries into Excel, it says : Nz is not recognized. So anyone knows a good replacement for Nz() that works in Excel as well?
I have linked an excel workbook as different tables in Access. The workbook contains a Microsoft Query. I have now found out how I can open and edit the workbook from access, how can I Update/refresh the MSQuery before I close it?
Here is what I have: Private Sub cmdTest1_Click() Dim MyXL As Object Set MyXL = CreateObject("Excel.Application") Set MyXLSheet = GetObject("c: est.xls") MyXL.Application.Visible = True MyXLSheet.Parent.Windows(1).Visible = True MyXL.Application.Cells(1, 1).Value = Now() MyXLSheet.Close SaveChanges:=True MyXL.Application.Quit Set MyXL = Nothing
Is it possible to compare rows, ie in excel I could do =A1=A2 to compare certain fields then if they are different/the same highlight.Is there a way of doing this in access?
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?
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;
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.
I regularly export the main table in our database to an excel spreadsheet, to provide an additional level of data back-up. (Using File/Export)
I was wondering if there is a way of automating this process, either through a command button on a form, or by linking it with shutting the database down.
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
Dear sir, Wish you all a happy X'mas . I wish to get some values while enter these tags in the form as shown in the excel format. The same thing i want to do in access but i am not aware how to do that can anyone please help me. It would be a great help if some one can explain me the steps as i am a beginer in doing access.
The alphabets AA,BB,CC... are what i will enter and should display the appropriate values for them as shon in excel coulms Please find the excel file attached to get a clear idea of what i am looking for. thanks & regards pillaisg
I really could need help with the using of the IIf function in the queries of MS access. I really need to know how to use them.. So please help me out as fast as possible...
I have a column-I containing 100 different values. I need to find the bigger one between 0 and each of those 100 numbers in column-I accordingly, and put the results in column II. I tried to use max function as I did in excel, but couldn't work it out. Help please? Thank you!
I'm currently importing a sheet from Excel into Access 2003 using Docmd.TransferSpreadsheet.
This works fine but requires the path of the file in question to be stated as one of the parameters. I have donjhe this and it works but, I would like the user to be able to navigate to the file using the standard open file dialog box.
I used to do this in oracle using RANK function. I do not know how to do this in MSACCESS
SELECT Tax_ID, Tax_Name, Start_Date, Tax_Percent, RANK() OVER (PARTITION BY Tax_Name ORDER BY Start_Date desc) as Date_rank FROM TaxType where start_date<=to_date(10/01/2010, 'mm/dd/yyyy')
I will firstly give you the low down on the business that I am doing the database for. It is a Car Restoration Business, where we deal with vintage/classic/muscle cars and restore them to the former glory.
I do not know Microsoft Access very well, I have attached a file with what I think is a databae I could be wrong. The main function of this database is to search for suppliers for a specific car part for a specific car.
So if I want to find a doorhandle for a 1970 Ford Mustang, I need the suppliers pertaining to those criterias to come up.
