Using Excel Function Forecast In Access?

Jul 12, 2013

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

[code]....

View Replies


ADVERTISEMENT

Need To Use Excel Function In Access

Jan 15, 2007

Hi,

I am trying to use the YIELD worksheet function in Access but can't get it to work. It comes with Error Sub or function not defined.

As per help file I have installed msowcf.dll but it still doesn't work.

Any ideas?

Thanks

View 12 Replies View Related

Queries :: Can Access Expression Builder Mirroring (IF Function) From Excel

Dec 12, 2013

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.

View 14 Replies View Related

Forecast Units

Feb 7, 2008

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.


I would need it to display:

W/E Units Boxes
2/23 2 24
3/1 2 24
3/8 2 24 etc....

Until the total units ordered equals 0

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.

Any help would be greatly appreciated.

View 9 Replies View Related

Query To Forecast The Vehicle Service Due Date

Feb 12, 2007

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

View 3 Replies View Related

Queries :: If Statement - Find Cumulative Forecast Up Until A Best Before Date

May 22, 2013

I want my query to find the cumulative forecast up until a best before date.

This is fine if a forecast goes past the best before date as the query picks it out but if the forecast doesn't continue then it won't match them up.

I need the IF statement to say that IF no forecast is present then look at the last cumulative forecast.

How would I go about this?

View 4 Replies View Related

Forms :: Create A Production Forecast Form Based On Previous Sales History?

Jul 19, 2014

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?

View 6 Replies View Related

Nz() Function Does Not Work With Excel

Nov 26, 2007

Hallo guys,

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?

With kind regards
Maxim

P.S. I use office 2003.

View 2 Replies View Related

Call A Function In Excel

Oct 29, 2004

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

End Sub

eroness

View 1 Replies View Related

General :: Replicating Excel Function

Oct 10, 2014

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?

View 1 Replies View Related

Access Query With Links To Excel / Export To XML And Back Into Excel

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

Queries :: Importing Excel Text Field Into A Table - CDate Function?

Apr 28, 2014

I am importing an excel text field into a table. I am then appending the data to another table. I am having a hard time with the Cdate function

Excel Date Field:
YYYY.MM.DD HH:MM:SS

Example Value:
2014.04.21 18:24:30

My Query Code:

NewDate: CDate(Left([DateQ],4)+"/"+Mid([DateQ],6,2)+"/"+Right([DateQ],2))

My Query Result:
4/30/2014

What it should be:
04/21/2014

View 12 Replies View Related

General :: Access Data Export Into Excel As Data Linked To Excel

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

Automate The "Export To Excel" Function?

May 10, 2007

Hi guys,

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.

Any ideas?

View 1 Replies View Related

Importing Excel To Access Keeping Relationships In Access Tables

Sep 13, 2007

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!

Emily

View 14 Replies View Related

Look Up Function In Access

Dec 25, 2007

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

View 1 Replies View Related

If Function In Access

Feb 27, 2006

I am trying to get a query to return certain words if a swimming time matches a certain criteria

i have tried the if function, whi i know how to use in a spreadsheet

how do i achieve an if function/or equivalent in a database??

jen

View 5 Replies View Related

Using IIF Function In MS Access..

Sep 24, 2006

Hey.

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...


please..

Hardik

View 2 Replies View Related

Access Max Function

May 14, 2007

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!

View 2 Replies View Related

How To Run A Search Function In MS Access

Sep 6, 2006

Hi there,
I would like to run a query which will allow me to search a specific column in a table. Any help would be greatly appreciated!!
Thanks

View 5 Replies View Related

Replace Function In Access

Aug 3, 2006

I'm trying to assign a numeric value to different existing text values in a field in Access. Does anyone know the syntax of this?? :) thanks

View 3 Replies View Related

Generate A Function Through Access

Aug 25, 2004

I have a talbe called LOOP in access. The fields in it ar appl,sales_table,title_code,and passalong.

From this table i want to autogenerate sql and run them in a vb function through access.

My first step was crating the sql. I did it using this query.


SELECT "DOCMD.RUNSQL "&"'"&"SELECT tsr,title "&title_code&" ord_time from "&sales_table&"'"

This generates the following vb command:
DOCMD.RUNSQL "SELECT tsr,title,q2_title,ord_time from appl_sales"

The genrated sql will be a insert into query but this is just an example.

Now this will give me the sql that i want. But I have no idea how to get that into a function.

Basically say it generates 10 different vb commands i need that to run as one function. Is there a way to do this through Access and VB.

Any help would be greatly appreciated.

Thanks

View 4 Replies View Related

Trim Function In Access?

Mar 30, 2005

Dear all:

Is there a trim function that exists in access vb so I can apply to a text box to remove irregular spaces in a textbox field?

I am importing an excel file and a column has names with irregular spaces in it. Hopefully this trim function can solve that issue.

Many thanks,

Regards,

Dion

View 2 Replies View Related

MS Access Browse Function

Mar 24, 2006

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.

Any help appreciated...AJP

View 7 Replies View Related

How To Use Rank Function In Ms Access

Dec 5, 2011

I trying to use Oracle Rank function in MS-ACCESS. How do I do that? Here is table and output I am looking for

Table: TaxType

Tax_no Tax_Name Start_Date Tax_Percent
----------------------------------------------------------------------
1 VAT 1/1/2008 2.3
2 VAT 1/1/2009 2.5
3 VAT 1/1/2010 2.6
4 REW 2/1/2008 1.6
5 REW 2/3/2009 4.3
6 OTH 3/1/2008 5.6
7 TGH 11/1/2009 6.7

If I pass a Date 10/1/2009 I need below result(ie maxdate of each Tax_Name with percentage)


1 VAT 1/1/2009 2.5
5 REW 2/3/2009 4.3
6 OTH 3/1/2008 5.6

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')

View 3 Replies View Related

Search Function In Microsoft Access

Jan 12, 2006

Hi everyone,

Thanks for taking the time to read this.

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.

How do I get this to happen?

Thankyou for your help

Jess

:confused:

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved