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
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]....
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.
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.
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
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 RelatedI 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?
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
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.
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?
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
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 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
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
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 RelatedHi 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
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 RelatedI 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
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
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
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')
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:
Does anyone know of a way to trigger a function in Access when a specific email is received in Outlook?
Thank you for reading this question and for those that reply, thank you for your time and knowledge.
I am trying to import a excel spreadsheet ( 3,000) rows into a access database. It has been working finr for over a year. When I do the import it gives the following message: I have attached part of the excele spread sheet.
'Master 7-20-07$'_ImportErrors Error Field Row
Type Conversion Failure pID 161
Type Conversion Failure pID 162
Type Conversion Failure pID 163
Type Conversion Failure pID 164
Type Conversion Failure pID 165
Type Conversion Failure pID 176
Type Conversion Failure pID 177
Type Conversion Failure pID 180
Type Conversion Failure pID 181
Type Conversion Failure pID 182
Type Conversion Failure pID 183
Type Conversion Failure pID 184
Type Conversion Failure pID 185
Type Conversion Failure pID 186
Type Conversion Failure pID 187
Type Conversion Failure pID 188
Type Conversion Failure pID 189
Can anyone tell me what is happening. I cannot even paste the spreadsheet into the DB.
Thanks Brian
Hi
I have a table which has information the count of students in classrooms around the university and I need to summarize the table by Faculty. Therefore, all I want to do is a count of students for each faculty i.e. Art and Design, Business and Law etc.
The query i put together is: SELECT Count([tbl_Audited Classroom for Week 02].Faculty) AS CountOfFaculty
FROM [tbl_Audited Classroom for Week 02]
HAVING (((Count([tbl_Audited Classroom for Week 02].Faculty))="BAL"));
So when I run the query I get the error message 'Data Type mismatch in criteria expression'. The Faculty field is a text field, so I don't know if that would make a difference.
Can you please help?
thanks
i'm trying to count the total number of records found for this query. i used the following expression (code) in the QED field row Total Incidents: Count([IncidentID]) which, result in an error message.
Can sum1 help me solve this problem. any help would be highly appreciated.