Forum: Excel and/or Access Help

I would like to use a button on a form to add set data directly to a table but the fields are number type and the only way I know to do it tries to add it as text type. I know there are other ways but for the end user it needs to be brainless for them to use itat least here. Can someone help me out. Here is code I have. Private Sub Ctl1_kortek_kit_Click() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("SELECT * FROM ", dbOpenDynaset) ' Dynaset to enable UPdating If rs.EOF And rs.BOF Then MsgBox "File is Empty" Else rs.AddNew rs("Part") = "kortek kit"

I have an Excel workbook where I used to import all the data from an Access table. Now I just want to link to the Access table and just pull small amounts of data from it. I just don't know the syntax to use in functions in my Excel workbook. When the data was imported into a sheet (called 'QuoteData') in the same workbook, the syntax for one of the fields I'm working on would read: =SUMIFS('QuoteData'!$H:$H,'QuoteData'!$J:$J,E$3,'QuoteData'!$E:$E,$A5) That worked fine. Now this data remains in the Access table (called 'QUOTE_INFO') and I've created a connection to that table. I've tried this (using column names instead of column letters --grasping at straws!) and it doesn't work: ...

Hi, I've been trying to import a csv file from excel into access using the DoCmd.TransferText acImportDelim method. I've looked at the following posts: http://www.ozgrid.com/forum/showthread.php?t=71318&page=1 http://www.ozgrid.com/forum/showthread.php?t=32942&page=1 However, when I've run my macro I get a "run-time error 2498 An expression you entered is the wrong data type for one of the arguments" on the DoCmd.TransferText line. snippets of my macro are below (I took out all the error checking and input validation lines).

Hi Everyone! I have an Access db named City County Vendor Spend Rpt and an Excel workbook named INDY STAT - Local Spend.xls. The workbook has the spend info from Access linked into it. On an Access form, I have a button that opens the workbook via a module, which I have attached as "City County Vendor Spend Rpt.txt". When I click on the Access button to open the workbook, I get "You attempted to open a database that is already open exclusively by user....." then it asks me if I want to connect to the database, "yes" takes me to Data link properties where I change it to "share deny none" and click "ok". Well, I don't want to do all that. I want Access to open the workbook and refresh the data automatically upon opening without any messages. I can get the workbook to open without the...

Hi, Please excuse my Access ignorance, I am a newbie to it but require a specific solution asap before I can get full proficient in it. I am currently creating a web database hosted on SharePoint 2010 for use by a number of team members. The database has a database of about 4000 records from which I need to pull most fields in a web form. I want a combo box or text box/search to pull up specific records one at a time in the form. For this web database the combo box wizard (that excuses my lack of knowledge) does not appear so I'm sort of stumped. I'd appreciate any help. Cheers

Hi, i have a problem which i can't solve ;( I have a logger which loggs some data and this data can be exported in to a excel file (you can download the file here http://www.2shared.com/document/ddphW3c8/ddd4.html). As you can see only one row is used (ROW F) it hase a lot of data, so what i need is an external excel file which can acces this one find the bigest value and from this value subtract F2 and of course on the end give me the final result... Is something like that possible or am i asking to much??? Thanks!!

Hi, I am new to the forum. I am trying to assign an Access recordset to a dynamic array in vba. While this works is there a simple way to transpose the dimensions and use base 1. It appears if you use the getrows method the dynamic array is using Base 0 and the upperbound of the first dimension is the columns from the record set vs. the rows. test() = adors.GetRows(adors.RecordCount) thx Howard

Hi, I am new to the forum. I am trying to assign an

Hi I am trying to create the simplest of Access reports. I have 2 queries each with 3 fields QrylAvailabilities Supplier Tonnes Month QryProgrammed

Hi everyone this is my first post since i've just recently got into VBA so an up front apology for anything i've done wrong! My issue is calling subroutines. when i make a command button on the spreadsheet and write all of my code in its window (by viewing its code and just writing it straight in) everything is fine. However when i want to call a different object's code (for example a user form) using the command button, the user form also wants all of the code in its 'view code' area. (meaning i can't put the command button script in the user form code and vice versa) I tried to get around this by making the command button call a subroutine at the top of the user form's script so it just flows from one to the other but it says i have not defined the sub or function everytime...

Hello, I have two crosstab queries, both have the same product numbers listed down the left side and then the fields are different on each query. One has Team 1 - Team 9 in the fields, and the other had 1-13 in the fields. I would like to create a report that just lists each product number and then under each product number it lists the name of the field from query 1 with the lowest value for that part number, and then list the name of the field from query 2 with the lowest value for that part number. This is very complicated for me to figure out, especially since this is my first week ever using Access. Any help would be greatly appreciated! Thanks in advance!

Hello OzGrid Members -- I have what I think is a straight-forward question in Access. I need to transpose a column based on values. Let me explain. INPUT TABLE (this is what I have already) Period CUSIP AmountA AmountB ... --------------------------------------------- 1 Q7T4 ... .... 2 Q7T4 ... .... 3 Q7T4 ... .... 1 C945 ... ....

I'm doing a YOY report where there is a main report with a field (Lets call it field A) that displays an amount based on the first year and then second year as it cycles through the groupings. Lets say the amount in field A for 2009 is 100 and for field A farher down the page for 2010 is 200. I then have two sub reports that amonst other things have a calculation that is dependant on the variable in Field A. Let's say the field in the first Subreport is Field B and in the second Subreport is C. When I do my calculation, which is a simple division of field B by field A on the first Subreport and then field C by field A on the second Subreport, it sees field A as the same amount each time (the 2010 amount of 200). How can I create the expression in field B to see the value as the 2009...

Hi there, My current problem is trying to create an Access Database for student grades. I have all the grades stored, by module, seperately in a couple of dozen different excel worksheets. After creating a practice database and a form to accompany it I soon realised that 'importing' the information into this form would be impossible seeing as how I can only currently take data from one table and seeing as the student does 4 different modules, how would the form know where to find this information - I can't very well create over 42 tables for each module, Can I? Any suggestions or is access simply not a good idea for this?

I am viewing a worksheet with the following Cell A1 shows 320+90 B1 shows 452+75 C1 shows 131+85 D1 shows 2.4972 The formula in C1 is B1-A1. The formula in D1 is C1/5280. It returns a mileage value. I see the values in A1, B1, and C1 are using a custom number format of 000+00 I know they are evaluating to the first number x 100 then adding the second number. Thus 320+90 evaluates to (320*100)+90 or 32090. Or it's like the + symbol is being ignored.

Hi I'm quite new to VBA coding and need some help desperately. i have the following code to get data from an Access database to Excel: Sub Access_Test() Dim acApp As Access.Application Dim dbMyDB As Database Dim rsMyRS As Recordset