Alright, I'm trying to read and parse a file that'll be outputted monthly by another system that i cannot change (csv would be my prefered choice) so i'll havta deal with what i got. I'm not entirely sure how to go about this, but my intent is to have a form in my database for a user to bring up a open "*.txt" which is a width limited text file. the other system outs puts this file with a header like the following:
Date: 06-08-06 [08:53] recdate4) Receipts by order, position and sequence Page: 1
CFM Production Company Comp: 100
Sorry about the ugly post of the outputted file, but it is width limited by 255 characters and has verticle bars and spaces sepearating the fields i want. I want to ignore the headings. in addition the column titles since i will already know where they're going to go (hard coding it). can someone explain how to parse line by line as a new record in access db?
I am trying to export a query from an Access 2007 database into a fixed width text file, to import into a statistical program. The query is a simple select query from one table, selecting 5 columns. I am using a specification to set the width of each of the fields (image attached). The problem is with the first field in the table which is a long integer field. I want to set it as a fixed width of 4 characters, but every time it exports it sets the width as 11 characters. I have tried exporting from the original table, changing the data type but nothing seems to work. Any help would be greatly appreciated. Thanks Laura
I am trying to export a text file to be imported in to excel but whenever I export as fixed width all by number columns get cut to 2 decimal places, does anybody know how to get around this?
I have a spec set up that controls the settings for an export file. the table and the form are set up with fields that are right justified. When the data is entered into the table from the form, it shows in the table as right justified. When I run a query and then try to export the results, I use the spec I set up to deliver the txt file in a fixed width ASCII txt file. The data shows in the txt file as left justified. - I need it to be right justified.
example:
$ amount- in the table, the dollar amount is 11 char long and is right justified do it looks like this: |-------7.50| I need those spaces (-) in the export file yet when I export it, it is left. The wizard shows a preview, yet there is no way to make it right.
Can I add blank spaces in the database or space fill the 7 preceding Char in the field with some sort of invisible char or zeros?
I am trying to export a table (and possibly later a query) into a fixed width text file and I am not finding where to set the specifications. When I select Text File in the export tab, I only get the check box about Export datq with formatting and layout. Is that basically it? I do have the table set up with the correct widths for the fields.
An additional question:
This is a large table with 99 fields for a total width of 804 characters. Is there any limit for the six of a record for such an export?
I have the following code to export a query into a excel file:
Code: Dim outputFileName As String outputFileName = "C:AccountSpreadsheet est.xls" DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Q_Search_Invoices", outputFileName, True Dim xlApp As Object Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True xlApp.Workbooks.Open "C:AccountSpreadsheet est.xls", True, False Set xlApp = Nothing
This works almost exactly how I want it to work.
The only thing wrong is that the columns are all the same width and they are all to narrow.
Is there a way to make the columns automatically become the width of the longest text within them (exactly what happens when you double click on the side of a squashed column in excel)?
What I am trying to accomplish is import data from a CSV file every month and have it stored as a table and have that table automatically be converted into a printable invoice. I can do the import with no problem. But I am at a bit of a loss as to how I can go about creating the database.
There is only one client that I have to make up an invoice for, but will be more in the future. I need to store the data for each months invoice and then get yearly reports off that data.
#1, How would I go about connecting each months data? #2, Should I be using a report to create the invoice layout? #3, How do I go about automating creating the invoice from the imported data? (I am at an intermediate level with VBA, but access seems to be a whole different ball game)
Basically im trying to setup a click button that will export my query as a text file ( the text file will then be fixed width and i have already setup the specifications for this).
I think i get how to complete the export part as below
OutFilePath = "file location i want the data to be exported to"
All, using 2010. I have a database that is split into FE and BE. The FE is on users desktop. The supervisor wants to give limit access to the database to one other user on the network without giving them access to the entire db. There is not an user table in the database or a login form at the moment. So what can I do to provide limited access to only one form?
I have created a query using the query builder by concatenationg several fields using the expression builder. Once concatenated, the total number of characters is greater than 255, and is therefore truncated. Since this is a query field and not a table field, there must be a way around this limitation. I can't even create the query and dump the results into a memo field because the dump will still only contain 255 char.
I could probably create the query using VBA, which creates a table containg a memo field, which is then populated by a variable containing the concatenated fields, but I would like a simpler solution.
Any ideas on how I can generate a query field that contains more than 255 char? The query is used to populate a report.
In importing data from Excel 2002 to Access 2000, I have several columns of text data that has more than 255 characters; however, importing into Access does not give me the option to redefine those data fields from "Text" to "Memo". As a result, Access truncates the data to 255 characters.
Is there anyway to work around this issue by still using the Excel file? I have saved the Excel file as an HTML file and this seems to work, but it is an extra step that I would like to avoid.
I was assigned by my manager to design an Access database system that is able to import all data from excel file monthly and creating charts & tables to analysis how each sales people and industry perform.
We originally have a big excel master sheet that has more than 10 sheets. I tried to import the current excel into access, but then i realized that this is not gonna work. because for next month, there will be new data and I can't do the whole import process over and over. Plus, after this system is designed, the users will be someone who has no knowledge in access, so i need to create a user-friendly system for them to use.
My questions is:since the data is always cumulative number, if I imported current excel file into access, when the next month comes, how to update the new data into excel. p.s. EXP. Mike's sale volume is different each month, and with the access system, for that column, it will be a cumulative number, like the total from the month of November to this month. how do i achieve this kind of update/import goal?I tried to link the excel to access, but by doing that, I will not be able to set relationship or change the attributes of any data type in access.
I have database that i am working on , i want to give a user a time limited accsess ( one week ) to the reports section for example !!
after a certain date he will require a password or the report button will be inactive ...
-at program start up , it will check for today's date if it is greater or equal to (lets say ) 24-9-2013 then it will change a field in an X table from "yes" to "no" ,
-after that the code will check table X for the yes no field , if yes then open Form A if no then open form B / or make report button inactive
how to do that in VB ... and is there is a better way to do it ? can it be done in the macro builder ?
Is there a way to use a convert use a "text to column" function in access that separates at a fixed width?I have an access table (that I can't change) that includes the data and time in the same column and I want to separate the two (date and time) in a query. I can't use the "Left" function because my date isn't a consistent 10 characters.
My boss wanted me to develop a way for a department to do a monthly report from Access. In the beginning, I set up a login for the user, then the user would input a start date and an end date to retreive the report. However, I just put part of the report up and will need to put what ever information they needed in the report. So, I did all the queries in Access and it produces correct result. Now I have to some how put it in ASP file including all those queries but dont know exactly how I would do it since I would not have a name for each query that I did in Access. Plus, in Access I used a fix date range to check the query and it works. So, I replaced 8/1/2005 with 'txtStartDate' and replaced 8/31/2005 with 'txtEndDate' and it works for one of the query. Now I when I looked at the rest of the queries I made in Access I would have to make some adjustment including the one that calculate the percentage from 2 queries. Below are several queries I made in Access but will need to somehow incroporate in ASP file.
(I replaced 8/1/2005 with 'txtStartDate' and 8/31/2005 with 'txtEndDate' in ASP file and it works. Need to do that with the rest of the queries) Query1 (count number of record in group A) Code:SELECT tblNatureOfCall.NatureDesc, SUM(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountofLogsFROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallIDGROUP BY tblNatureOfCall.NatureDesc;
Query1Total (Sum of count number of record of Query1 group) Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfDispositionOfPatientID, tblDispositionOfPatient.DispositionFROM tblDispositionOfPatient INNER JOIN tblCallLog ON tblDispositionOfPatient.ID=tblCallLog.DispositionO fPatientIDWHERE tblDispositionOfPatient.Disposition="TRANS"GROUP BY tblDispositionOfPatient.Disposition;
Query1Percent (This find the % from Query1 and Query1Total. Need a code to format in % form as 34.00 not .3400) Code:SELECT (SUM(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)))/Total AS Expr1FROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallIDGROUP BY tblNatureOfCall.NatureDesc;
Query1TotalPercent (This find the percent of a group out of the total of 2 groups) Code:SELECT [Query1Total]![CountOfDispositionOfPatientID]/[TotalQuery]![Total] AS Expr1FROM TotalQuery, Query1Total;
Query2 (count number of record in Group B) Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfDispositionOfPatientID, tblDispositionOfPatient.DispositionFROM tblDispositionOfPatient LEFT JOIN tblCallLog ON tblDispositionOfPatient.ID=tblCallLog.DispositionO fPatientIDWHERE tblDispositionOfPatient.Disposition<>"TRANS"GROUP BY tblDispositionOfPatient.Disposition;
Query2Total (Sum of count number of record in Group B) Code:SELECT Sum(Query2.CountOfDispositionOfPatientID) AS PatientIDTotalFROM Query2;
Query3 (count number of record of group C) Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS CountOfPatientDestinationID, tblHospital.HospitalNameFROM tblHospital LEFT JOIN tblCallLog ON (tblHospital.ID = tblCallLog.PatientDestinationID) AND (tblHospital.ID = tblCallLog.NotifyHospitalID)WHERE tblHospital.HospitalName <> "HCO"GROUP BY tblHospital.HospitalName;
TotalQuery (sum of Query1 count number of record and Query2 count number of records) Code:SELECT Sum(IIf([dateofcall]>=#8/1/2005# And [dateofcall]<=#8/31/2005#,1,0)) AS TotalFROM tblNatureOfCall LEFT JOIN tblCallLog ON tblNatureOfCall.ID=tblCallLog.NatureOfCallID;
These are the queries done in Access but will need to incroporate in ASP. Query1 is already in ASP, just need to add the rest of the queries. Dont know how I would do it.
Hi I am a noob and I'm having problems performing a particuler form.
I need a form that will auto-increment records as they are ented from 1 to 20 but once I have receached 20 I need it to start over at 1. So no record will have a higher number than 20 in this particular field.
I often create contract using mail merge. I have an access file that I want to use as data source for word file. But it does not automatically.
Please download the attached file !
If there are 1 customer and 1 property, I do not need to do anything. Conversely, if there are many customers and many properties, I take time to manipulate.
Firstly, I open the word file. I have to copy and paste paragraphs that I want. Highlight of the original paragraphs is blue.
Secondly, I click 'Insert Word Field' -> select 'Next Record'.
In short, I wish to use VBA in access file to automatically perform the steps that I have outlined.
I have a button in the form of the current access file. What I want to do is when the user click on the button, triggered the vba to backup current access file. But since the file is currently open, is this possible? If yes, may I know how to do it with vba?
I have tried two methods but failed: 1) Use copytofile method, but I get permission denied due to file open; 2) Use dbengine.compactdatabase method, but it also has problem if the file is open.
I'm experimenting with MS Access, and I've successfully split an MDB someone created into two files. I can access the database from 2 computers at once using the front end file connecting to a file server which hosts the _be.mdb file.
Could the _be.mdb file theoretically be on a NAS without any computer attached, or is there some runtime or full version of Access that needs to run on the machine with the back end file?
I have a query that returns a set of records which details stock items that are older than a date given in a form. However i need to limit the results to the number of items held in stock i.e say i want to look at stock over 1 year old, i get a list of all the stockids, and the date added. Say there is a stock level of 3 for a particular stockid the results should be limited to the first 3 records that are over 1 year old. Rather than the whole list of dates I get now.
Any help or direction with searching terms would be appreciated
I was wondering, in a form (to add records in the Table) with a date field, can I limit that field to only previous dates. This will reduce errors in the table, no future dates anymore.
Hello, On a form I have a way to search for a record by using a listbox that has a Distinct Row Query from the table that the main table that the form is bound to. It looks for the Sample ID's that are associated with the samples that we test. The list ends at record 87877. We are WAY past that number in our numbering scheme but the list box does not display all the records. When the users type anything over 87877 the auto complete doesn't work and the last record shown in the list is 87877. Does anyone have any idea how to make all records show? The users use this to navigate quickly between samples but now it is broken. Is there some sort of limit? Thanks Greg
Hi, I am in need of help to sort out some records. I have tow existing queries I would like to combine and get one final set of records out of. They go like this:
Query 1. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 x x (check Y ACD 2 x x boxes)
Query 2. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 10/8/05 (query Y ACD 2 8/8/05 9/8/05 10/8/05 performs calculations)
What I am looking to retrive through the third query is this:
Query 3. (unique #) Lot Protocol Sample # 1 mth 2 mth 3 mth X ABC 1 8/8/05 9/8/05 Y ACD 2 8/8/05 10/8/05
Where the third query only shows the calculated dates when the check box is true. So for month 1 & 2 I get dates for Lot X and for Lot Y I get dates for month 1 & 3. I have tried to go through the expression builder, but to no avail. I either get all records, like query 2 or I get nothing reported. I am not sure how to limit the records based on the check boxes.
