I'm trying to import a text file with 273 fields into two tables. I've been able to do this with the code I found on an old thread and I'm now trying to accomplish everything with one step. The file I'm importing is tab delimited text file. With this current code I'm only able to populate the first record in the table and then I get error message. (Run-time error '3265') (Item cannot be found in the collection corresponding to the reqested name or ordinal).
Code:
Public Sub ImportTextFile() ' to use the ADODB.Recordset, be sure you have a reference set to ADO Dim rst As ADODb.Recordset Dim rst2 As ADODb.Recordset Dim strFile As String Dim strInput As String Dim varSplit As Variant Dim intCount As Integer
I'm trying to make a database to track inventory or several items. Basically, I have four tables:
1) RawMaterialList - includes a list of all raw materials. 2) PartList - includes a list of all finished product using said raw materials. 3) RawMaterialRecieving - contains details from each packing slip of incoming raw materials. 4) ShipmentRecord - contains details of daily shipments.
Each of these tables is fed by a form of the same name. I should note at this point that I basically taught myself how to use Access and I imagine I'm in the dark about quite a few things it can do. I've made several databases over the last few years, but I'm stumped at this point.
Here's my problem. In the form RawMaterialReceiving, I have several fields aside from basic information:
1) Item - a list of of raw materials from table RawMaterialList 2) Description - also dependent on info entered into table RawMaterialList 3) Quantity
My problem is I want to add up the quantities of each raw material and I'm not sure how to go about that. Lets say on May 13, I received 15pcs of Part A and 20pcs of Part B. I enter this information as Item1 and Item2 respectively. On May 14, I received 30pcs of Part B. I enter this information under Item1. Now I want to add up all of Part B (50 pcs). But Part B has one value listed in the field Quantity1 and one value listed in the field Quantity2.
I have 3 queries named Mech Final Equipment 3 Mth, Mech Final Equipment 6 Mth, and Mech Historical Final Equipment.They all have two fields-Final equipment and Sum of Sum of Down (calculating the number of minutes each piece of equipment was down in the time period selected).
My ultimate goal is to join the three queries to display a pivot chart that uses the Final Equipment as the category field and 3 Mth, 6Mth, and Historical as seperate data fields.What I have is a join query (Which I have named Mech Final Equipment H63 Joined)
Using this SQL:
Code:
SELECT DISTINCTROW [Mech Final Equipment 3 Mth].[Final Equipment], Sum([Mech Final Equipment 3 Mth].[Sum Of Down]) AS Duration FROM [Mech Final Equipment 3 Mth] GROUP BY [Mech Final Equipment 3 Mth].[Final Equipment] UNION
[code]...
Which returns a table that looks like this:
Final Equipment, Duration
Ancillary Equipment, 225 Ancillary Equipment, 401 Ancillary Equipment, 1787 Brush Unit , 1252 Brush Unit , 2519 Brush Unit , 8004
And so on.What I need the table to look like is this
Final Equipment, 3 Mth, 6 Mth, Historical
Ancillary Equipment, 225, 401, 1787 Brush Unit, 1252, 2519, 8004
And so on, like a cross tab.I tried to do a crosstab query but I don't have enough fields.
I have been given the task of organising a mail-merge with a sharepoint list, but the names and emails attached to each object are seperated by a delimiter. Furthermore each person is associated with many objects, and they want the merge programmed to only send 1 email to each person.
So what I need to do is split the data in one column into three separate columns, and then perform a concatenate. The concatenate isn't an issue, but how to split the data into 3 new columns within Access?
If worst comes to worst I'll tell them they will have to use the text-to-columns function in excel first, but would like to try and avoid that where possible.
I have 1 table that I duplicated to make 3 tables total. I did this b/c I am trying to create separate columns from the same field and table. The field is Workorder. Each workorder list the workorder number followed by a dash and then code. I am wanting to put all of the workorders with the same code in it's own column.
I have 5 codes that i am searching for. The first column list the workorder and a code (123456789-AD). The second column (123456789-BC). I'm good to this point but my problem occurs next.
The third column i am trying to put 3 types of workorder and it's code in the same column. As follows, (123456789-CD, 123456789-TC, and 123456789-PTC. However, when I do this it takes the results from the 3rd column and applies it to the 3rd column but also the 1st and 2nd column. I tried a UNION query and unless I am doing it incorrectly it does not work.
I am trying to find the latest date in a table where the dates are in 2 separate columns and multiple rows. (there are business reasons why there are 2 dates per row they represent different but comparable activities)
I have a table "Assessment tracker" with the following structure
Name Type Candidate short text Unit short text EV1 Date Date EV2 Date Date
My Data:
Candidate Unit EV1Date EV2 Date TH1 10 07/05/2015 25/05/15 TH1 10 07/05/2015 07/06/15
I have a query "Candidate AC Dates" that compares the 2 dates EV1 and EV2 and outputs a 3rd column with the latest date.
It does this by using a function shamelessly copied from the web somewhere...
Function Maxdate(ParamArray FieldArray() As Variant) ' Declare the two local variables. Dim I As Integer Dim currentVal As Date' Set the variable currentVal equal to the array of values. currentVal = FieldArray(0) ' Cycle through each value from the row to find the largest.
[Code]....
This is working well (I think)
I then want to find the latest date for the 2 records i.e. the Max value for the Achdate.
Query: SELECT [Candidate AC Dates].Candidate AS Expr1, [Candidate AC Dates].Unit AS Expr2, Max([Candidate AC Dates].Achdate) AS MaxOfAchdate FROM [Candidate AC Dates] GROUP BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit ORDER BY [Candidate AC Dates].Candidate, [Candidate AC Dates].Unit, Max([Candidate AC Dates].Achdate) DESC;
But this is returning
Candidate Unit MaxOfAchdate TH1 1025/05/2015
I expect it to return
Candidate UnitMaxOfAchdate TH1 10 07/06/2015
It looks to me like MAX is considering only the day value rather than the whole date. I suspect this is because it is considering the results of the function in the first query as a short text rather than a date field. (I've tried to force this through declaring the variables as dates but don't know where else to force this. (I am UK based hence the DD/MM/YYYY format)
Hey, does anyone know how to set up a formula that will allow me to turn either one column on, or another, but not both at the same time for data entry eg. one column for dollar figures and another for sterling currencies?
I have a Microsoft Access table with the following columns: A,B,C,D,E,F. In first row of Column A, I have the following string value: "Al,Peggy,Kelly,Bud,Buck" What I would like to do is parse this string as such:
Is there a function or query that I can split a field into different columns?
For example, I have First Name, Last Name, Address.
But I want to split the address field into Address1, Address2, Address3, Address4 as the initial Address field has a lot of characters with commas e.g. 11 London Road, Liketown, Likeshire, London.
So, I want to be Address1: 11 London Road, Address2: Liketown, Address3: Likeshire, Address4: London.
I thought that I can export the field using a simple query and then re-import it with using the text field into columns option, but it is time consuming.
I have a table by name "newtab" and I was trying to import a tab delimited text file "newdata.txt" into newtab. The first line in the text file are the column names: SSN, Lastname, FirstName (all tab delimited though). The same field names exist in the destination file. However I am getting the error which says the "the field name SSN Lastname FirstName does not exist in the destination file" What could possibly be the problem? Since the field names are not separated in the error message, could it be that it is seeing all 3 field names as one and therefore cannot match them to the destination fields? Does that mean TAB cannot be used as the delimiter? Using the interactive IMPORT from access directly for the same files work really good though. However, I would like to do this programmatically since the files would be coming in weekly for me to load and they are many such files. The command I used is as below. Please I need help.
I used the get external data tab and went thru the process. everything looked good in the preview but when I clicked finish I got type mismatch errors and the data that was supposed to be in field 1 was in fieild 2 and so on.
Think there are 50 comma separations all together. Anyway when I go to import / link my csv into access the data that is on the first row should it create individual fields where a comma has been placed... But it has doesnt quite worked, some of the fields are created and the rest have been put on a separate row! Rather than going to a new field. Rather having 50 fields I've got 21 fields and 3 rows or delimited text...
I had to delete a database so I exported selected records using export as a text file thinking I would get some sort of delimited text file that I could later use. Instead I got some sort of fixed length file with .............. between records and | between fields.
I'd like to convert this data to a tab delimited file.
With microsofts article, I have made to store multi options value of a list box in a text box with comma. However, since these are IDs being stored, I want these values to run a query and get results also.
But I am confused since have never used comma like in query
I am able to successfully export data from an Access 2010 Query to a Tab Delimited Text file without difficulty.My problem is that the Query includes several 'tick boxes'. The resulting text file shows the text boxes as 1 or 0 as appropriate. What I actually require is a Y/N result.To achieve the required Y/N result requires some fiddly find and replace editing which is complicated by the fact that the query also contains telephone numbers incorporating 1 & 0, This then requires further editing of individual records to convert misplaced Y/N back to 1/0. Is their any way that one can force the export to convert text boxes to Y/N rather than 1/0.
I have a database with a form that has 3 fields in a subform. I need to paste there data from multi rows. Now I have to do this column by column and paying attention that I select them correctly. Is there a way to directly insert the data by selecting only 1 field and have the data directly there as it would in excel?
Now we use excel as a "between" road to paste it there and then copy it from excel and paste it into access.
I have a database with all the hours employees have logged stored in the database. Our payroll company wants an excel spreadsheet that has very specific info in particular columns and fields on the excel spreadsheet, so I'm trying to design a query which will put the correct info in the correct fields per their system.
The challenge is, I have currently a query with Employee ID, Overtime Hours, and Regular Hours as separate columns.
I need to translate this to a query with a single column for hours and a separate column that designates those hours as OT or Reg, with two rows for those employees who have both types.
I don't know how to create a query or a formula in a query to break out each employee row into multiple rows with different data in the hours column. It seems like there's something pretty straightforward that I've done in a similar vein but it doesn't seem to work - I can do the opposite and combine those hours by using the SUM function in a query, but I can't seem to break it out this way.
I need to create a command button to import a tab delimited text file into a table in Microsoft Access using VBA Code.
I have set up the button however I am unsure as to how I should approach it and what code i need. If anybody has any suggestions I would be very grateful.
I'm trying to import a pipe delimited text file into a table. I can import the entire table using the following code, but I only get one column of data (the entire data set in one column). If possible I would like to import with the columns defined or if not possible use some code for a function similar to text to columns.
A little background. I need to export the results of a query I use to build a report. For Print Master software I need the "Field Names" in the text file as well as the data for a Mail Merge in Print Master (PM).
"The field name information in the file you have specified is missing or not correctly formatted. The first line of the file must contain the database field names. Make sure the "Export Field Names" (or similar) option is selected in the program from which you are exporting data."
Trouble is, when trying to export the report or query, Access has no "Export Field Names" option. It works if I first export to Excel and then from Excel to "txt" then to Printmaster. I would like to eliminate the Excel step. Therefore, how do or can I get Access Export to transfer the "Field Names" along with the field data?
Ive been converting .xls files to Access database files. I would like to use ACCESS to develop the Reports but the client wants the Reports to look like those in EXCEL..eg. where you have lines between rows and columns. If I could give my Access Reports the same look and feel, I could wean these guys off of Excel and into the wonderful world of relational models.
Does anyone have sqlcode or tricks I might use to create the Excel 'look alike' report in Access?
What I'm stuck on is the filepath. The file path changes everytime. So I would like to have either the open dialog box (I've tried many different versions that I found on the web.) or to search by the account name for the folder and place the text file in there.
Here is one that is closely working how I want it to:
This is a function that I found, that opens a dialog box for the user to select the folder location. It works, but I can't seem to get it to work properly.
It prompts, the location, then once you select it and press ok. It will add the folder name to the full file name, and place the file in the default root path. Not the selected folder path.
So in the end it will look like this:
D:1_MainMyFolderName_MyTextFileName.txt
I'm somehow stuck on getting to seperate the file path from the file name, so you it look like this:
D:1_MainMyFolderNameMyTextFileName.txt
Code:
Dim MSg As String Dim SelectedDir As String Dim SelectedDirFinal As String Dim SelectedDirName As String Dim StrFolder As String
[Code] ....
I think it should be something very easy, that I just need a pair of fresh eyes to look.
I've tried the Fileobject, FileFolder method, but can't get the quite work properly.
I have a table (let's call it "tableA) with about 7 fields. One of the fields contains information that I need to do a query on so that I may find specific payees, for example:
The field is called "Payee" and in that field for each record, the information could be "At&T", "A T and T" or "Abercrombie & Fi", but there is also other information in that field along with At&T, etc., so the field for a record may look like this: A T AND T MOBILI A T AND T MOBILI 10 90034 Bill Pay or ABERCROMBIE & FI 3042 PP 10 9111 DIRECT
Is there a way to set up another table (let's call it tableB) to have all the Payees names (such as AT&T or whatever) in it and by the push of a button (maybe macro), have a query go out and find all matches from tableB in tablea?
So, basically have the query look in tableA and find all the matches from TableB. Please understand that tableA could have records that contain the same name, but spelled a number of different ways, for example, AT&T or A T and T or ATandT or ATT. Again these names may or may not be embedded in with other information in a field.
Or is there a way that an Access query can look into an external list and find the records, example:
Have a text (file) list with all the names (separated by a comma or space - AT&T, AT & T, Amercrombie and FI, Abercom&Fitch, etc. Now have a query go to that list and find all records that match in my tableA?