I searched the forums and couldnt find anything that directly related to my issue. What I am trying to do is export a fixed-width text file, but the issue is that the file has to be line feed only. By default Acces creates CR/LF and the client's import specification only allows for Line feed.
I am currently using a macro, with export specification, to create the file. Due to time constraints and other obligations, I am really trying to avoid VBA programming.
I'm trying to export a table to .csv file, but I want fields with null values to be padded with spaces. ie. if the field length is 50, but there is a null value, I want all 50 spaces.
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 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?
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"
e.g: InputText is 123, InputText2 is abc I wanted to display the text inside textbox of frmCalendar as: 123 abc instead of 123abc. how shall i use the vbCrLf?
I have a table I need to export most fields are left align but there are two that are right align, I have tried to use the Space Function but it does not work well, I have also tried using the Rset in a vba module and thought it was working but now it is not.
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 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
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.
I hope someone can help with this one. After many years of using Access for ad-hoc data conversion this has beaten me. I need to produce an ascii text file with fixed column widths, separated by commas, strange I know but the customer is always right. As it is fixed width I have inserted the commas by using a separate column for each one. Numeric columns need to be left padded with zeros. I have constructed a query to do all the column selection and reformatting into a new table which I then export using a fixed length export file spec. Everything works fine except for 3 columns which are calculated by subtracting one column from another. I can get the data to look fine in the output table, the datatype is text, but when I export the table the leading zeros are stripped. This is my expression: String(9-Len(FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0)),"0") & FormatNumber([FULL_FARE_EQUIV]-[TAX_EQUIV],2,0,0,0). The result in the table is exactly what I want: 000200.00 but when I export it I get a left adjusted 200.00. I've tried using format with a "000000.00" mask which gives the same results. I've tried removing the preceding comma column and including the comma as a prefix using the format mask ",000000.00" and also by concatenation. This looks fine in the table column ,000200.00 but I get an error when I export the table which blanks the column. Error attached.
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)?
Last monday's [Feed Inv]+ this weeks [Feed Del] - this weeks [Feed Inv]= Feed Consumed
Table:
Autonumber (is PK) Date Farm Barn Lbs Del (pounds delivered) Lbs Inv (pounds inventoried, this is a physical inventory done at farm)
I can use Between[Start Date] And [End Date] in the criteria to show this weeks Lbs Del and Lbs Inv in a query, no problem. How do I tell the query or report to subtract 7 from the Start Date to pull last weeks feed inventory?
The result will be displayed in a report, so calculating in a report would be ideal.
I have a Sub form titled Training that has the the following two fields:
[Department],[Jobs]
The jobs is a pull down select box that contains all of the job titles for the entire plant. What i would like to happen is for the user to be forced to select the appropriate department first and then use the [Department] selected to feed the criteria to the query that the [Jobs] field is located in. In addition i have been un-able to get the fields to be locked until the [Department] field is entered. I have tried the following unsuccessfully:
Jobs Query Criteria: =[Foms]![Training]![Department]
I've built an HTML web form that sends an email to me upon submission with all the field inputs. I have previously found success using VBA in Outlook to parse out these emails and feed the substrings into an Excel Sheet (hooray!), but now am looking to feed the substrings into Access, specifically into a table in an existing .mdb file. I figure the way Outlook will "talk to" Access will likely be a bit different from the way it does for Excel.
The way my previous script engaged with Excel was through a rule; every time a new email was received, the following script would run if the message had the subject line generated by my web form. The script would check whether Excel and the target workbook/sheet was open, act accordingly, and then input the substrings (I've excluded that part below), then return Excel and the wb/ws to their initial state. I imagine though, that with Access I might be able to feed the substrings into the .mdb without having to open it proper, the way a front-end talks to a back-end. But perhaps I'm mistaken on that front.
Code: Option Explicit Option Compare Text Public Const xlUp As Integer = -4162 'I genuinely do not know what this is _ about. It came with the initial _ borrowed code. Public Const wbPath As String = "C:...Workbook.xlsx" 'Workbook path
I am using a public function to feed a variable string to a query. So far I have got:
Code: Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String Dim cslStr1 As String, newStreet As String newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line with me.graph47 .SeriesCollection(1).border.Color = vbblue ' change the line color .SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3 .SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4 .SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color, .SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
I have a query that gives me a table with one column, say column A. I need to add 2 columns to the table and the coulmns will have fixed value. So I need to add column B and column C to my table and all rows in column B will have a fixed value and all rows in column C will have a fixed value.
What I have: Table with one column Column A a b c
What I need: Table with 3 columns: ColumnA Column B Column C a 12 14 b 12 14 c 12 14
