I have imported some data from a csv file that is generated by a machine. The problem is this the first row of data is the time date stamp and the second, third, forth.... is the live data at that time and date:
What I would like to end up with is this:
Time;Date;Data1;Data2;
10:00;30/11/2013;
10:00;30/11/2013;2000.00;3000.00
10:00;30/11/2013;2100.00;3100.00
As there are some 10,000 records I would like to automate this and have dabbled with update queries and I am not getting far. how to do this either at the import stage or inside access.
Does anybody know of a quick and easy way of preventing data insertion to a table from a form, so that table doesn't get updated unless all form fields are populated?
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.
My question is when exporting an access table to a text file using a fixed file format can you combine two fields into one field and if so how do you do it.
I can manually transfer the data ie thru File --> Get External Data etc but I can't seem to get the above statement to work --- with the same specification!!
In Access column name is STKITEMNBR and data type is TEXT. 4/5 of data are numeric and 1/5 are alfa-numeric. One of data was 15E10 in Access, but was altered to 1.50E+11 when exporting out to Excel csv file. Because it was Stock Item Number it needed to stay the same as 15E10 in csv file.
FDL00.... should be generated automatically, starting with 101 for 1st container and 201 for 2nd and so forth. The output should be in vertical manner.
Looking for a way to use transfertext to make a table and create a spec file from csv files I am importing. I want all fields in the table to be shorttext.
Otherwise I need another way to bring the files into a table.
I have a generic spec to use that brings in any data as text.
Created the spec from a delimited file with 255 text fields... Field001, Field002, etc.
However the field names need to be changed. My field names are in the first row of a table. I will need to read each fields data in the first row and rename the field.
I'm currently working on a project within access which will allow users to select/input information in a form which will then save to the database.
What I need is to create a button on the form which will allow you to convert the data collected from the form to a custom text file template.
(Or maybe the last row added to the database. - although this may cause errors. Example: the form information doesn't save which will in turn create a text file with the data collected from the previous job)
The custom text file template should have a sentence description followed by the value recorded from the form.
Example:
1 configuration type: (data from form)
2 model type: (data from form)
3 Poc: (data from form)
And so on.
So in summary - I would be to use the form to collect the desired information which will save to the database.
Once actioned, a custom text file should be populated with the collected information (with specific data placement as per the example above)....
Managed to export a table to csv file. The problem I now have is I need to add the following Text at the start of the file:-
H,LKJ85485524,DE
and the new last line :- T,whatever the record count is,2
Whether this info is added before or after export is not important.
I have even resorted to using VBA to create an Excel spreadsheet and saving to csv.
Thought this had cracked it however I had surplus commas at head and footer where the original csv file had additional columns (ok manually delete these comas) ,then found that the end of each csv line was missing a final comma that is needed by the recipient of the file.
I have cascading comboboxes on my search form. The first selects a year, the second all hospitals with patients who had visits in that year, and the third is supposed to be populated with all patients who had visits at that hospital in that year. When I click on the third combobox, the results are incomplete. Some of the patients fitting the criteria are listed but not all of them. On each run there may be a different number of patients. If I just run the query by itself, all of the patients are listed. The combobox rowsource is:
SELECT DISTINCTROW [PatientNbr] & " - " & [PatientName] & " - " & [ServiceYear], [PatientNbr] FROM qryFindPatients ORDER BY [PatientName], [ServiceYear];;
The query (qryFindPatients) is: SELECT tblSUR2941Master.ServiceYear, tblSUR2941Master.ProviderNbr, tblSUR2941Master.ProviderName, tblInterPatients.PatientName, tblSUR2941Master.PatientNbr FROM (tblSUR2941Master INNER JOIN tblInterPatients ON tblSUR2941Master.PatientNbr = tblInterPatients.MedicaidNbr) INNER JOIN tblInterPatientsEntries ON tblInterPatients.MedicaidNbr = tblInterPatientsEntries.MedicaidNbr WHERE (((tblSUR2941Master.Sampled)="1")) GROUP BY tblSUR2941Master.ServiceYear, tblSUR2941Master.ProviderNbr, tblSUR2941Master.ProviderName, tblInterPatients.PatientName, tblSUR2941Master.PatientNbr HAVING (((tblSUR2941Master.ServiceYear)=[Forms]![frmIntermediateVisits]![txtRevYear]) AND ((tblSUR2941Master.ProviderName)=[Forms]![frmIntermediateVisits]![cboFacility])) ORDER BY tblSUR2941Master.ServiceYear, tblSUR2941Master.ProviderName, tblInterPatients.PatientName; How can I get all the patients to show up in the combobox? Thanks in advance.
I have a query which exports to a excel file afterwards, however, it's not capturing all the data I need.My query is at the bottom, and I think the problem is the "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" statement, as this means that it'll never pick up a Cost Centre starting with "Z", as they don't exist in [tbl Master] (they're exceptions to the rule basically).
What I need to do is only parse the statement "(([tbl Master].ID)=[tblRequest].[Cost Centre]))" if the Cost Centre doesn't start with "Z", if it does, then it'll find it in the table, as there's validation on the point of entry that don't start with it.
Code:
SELECT tblRequest.RequestID, 'N/A' AS [Week No], tblRequest.[Refund Date] AS [Date], tblRequest.Requester, tblRequest.Authoriser, tblRequest.Refunder AS Processor, tblRequest.[CRIS Reference] AS [CRIS Ref No], tblRequest.[Customer Title] & ' ' & [Customer Name] AS [Customer's Name],
I've got the forms and queries in one .accdb file and my tables in a separate .accdb file. The forms file links to the tables in the tables file. The tables file resides in a folder called simply enough C:acc_tables and thats where i browsed to (obviously) when i set up the linkage.
One of the users does not wish (for whatever goddamn reason) to create a C:acc_tables file to stick the tables file in , and wants the tables file in some other folder.. Unfortunately this user does not have the skills to delete the existing links and re-link to the tables file after putting it in the folder he wants.
Where in the file that holds the forms and and queries do i find the path setting to the tables linked file? Can it be changed without deleting links and then re-linking?
I have a date field NxtAPayDate that I need to update to the same day in the next calendar quarter. I have an update query set up using DATEADD but it does not change the dates. The table name is AutoPay
I am using the Query Design Grid. This is the SQL code behind the query:
UPDATE AutoPay SET AutoPay.NxtAPayDate = DateAdd("q",1,[AutoPay]![NxtAPayDate]);
When I view the results the dates are the same - original dates, when I try to sort the results, I get a message box with the following: syntax error, incomplete query clause?
I cannot get the dates to change by using a specific date either
UPDATE AutoPay SET AutoPay.NxtAPayDate = #1/2/2014#;
I have a popup form that user will select for updating 4 fields in table. I have a update button that has code behind it that verifies the record is complete before closing the popup form. That works well.
However, I want to add an abort button that will close the form and save nothing entered however, I getting incomplete records and blank records. How can I code the button to not update the table and just close the form?
Is it possible to follow a hyperlink to a file without using a file extension?
I have links being created based on the name of a file, but because I haven't used a file extension it crashes.
I know I can give the user a choice of what the file extension is and add it to the hyperlink, but it's an extra step, and another place for someone to make a mistake.
I'm potentially using 3 different file types, Word, Excel and PDF's.
The hyperlink works if I just reference a drive/folder, or if I add the file extension.
I use a batch file to distribute the front end of a database application. Currently, everytime the user clicks the shortcut on her desktop, the batch file executes and copies the front end from a network location to user's local machine. The FE is updated like every couple of days. The users run the database more frequently.
I would like to modify the batch file so that it checks some attribute of the FE file to decide whether it needs to be updated. I can't rely on file size, since the FE includes temporary tables. I essentially need something like the "tag" property of form controls, only for files. It would be ideal if it was me who sets this property, like "version number". Except it has read without opening the file itself.
I need code for save dialog file ,and select the file from textbox and save it to the selected location.i have only this code and i dont know what else i can do with this because it just opens the save file dialog !
I am really stuck. I have spent two days searcinh different forums trying to solve my problem. I am trying to create an UPDATE q to my Access database. But I get either the: "Syntax error in query. Incomplete query clause" or "Syntax error in UPDATE query".
First of all here's the URL: www.innotec-as.no/login/Kunder Login U/P either: "alfen" or "thomas".
The page opening up shows the user info, U/P and adress. viewing the information is working perfectly - but editing it..no way.
When editing and submiting the data the above errors occour. Try that and you'll also see the SQL I am trying to execute. The CODE is as follows:
I have created a copy of an access file to make some changes to tables, forms and etc. Some of them are deleted, new ones created or the codes are changed.
Is there a way that I can apply all the changes to the original file?
I replied to post of Exporting to the same XLS file on 1/4, but didn't receive a reply. I need to export out from one table to Excel grouped by client ID to each worksheet. The Excel report will have approximately 15-20 worksheets. The DoCmd.Output exports each client to their own worksheet. Is it possible for this command to export one report with multiple worksheets (one for each client)? If so, will the DoCmd.Output work and how will the group by client criteria be implemented in the code?
I have an old program with ade file extension and I would like to add some filters in one of the forms but I don't have design view or layout view option.
I am trying to copy a pdf file between 2 directories. I am using xCopy, When I build my copy statement based upon various parameters, it looks something like this:
I might note that there is only one file in C:PDF995OUTPUT at a time. So I copy what is there, delete it and it is empty until the next usage.when it executes the statement, I get a DOS message asking whether what is being copied is a File or a Directory.How can I identify that without needing to respond to a message?
