If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Access Query Right Align
missing 1 requested field please
DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" text export subroutine for you. We are real busy here preparing to convert from ISBN's with 10 digits to new 13 digits, or else one should just have a routine that accepts --SpecID for tblSaveIMEXColumns (where one more field was added to it say "RightAlign" Yes/No, default 0) --recordset of data --path/filename of text file It's not hard to print a string you prepare yourself to a text file. Dim hFile As Long Dim strPath As String Dim strTextLine As String Dim rst As DAO.Recordset strPath = "C:\xxx.txt" strSQL = "SELECT ...." Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close "Donna" wrote: It still left-aligns the field. Here is what I put in the "field name" in the design view of the query: amt: Format([true-amt],"000000.00") It looks great all thru the export after I apply the export specification, but when the .txt file is created it is left aligned. "John Vinson" wrote: On Tue, 11 Jul 2006 09:54:01 -0700, Donna wrote: This export has to be in a specific format because it is going to be imported into the accounts payable system. Is there no way to right align the number field??? You can use the Format() function to convert the number to a text string. For example, an expression like ExpNum: Format([numberfield], "00000000.00") will convert the number to an 8 digit zero filled number with two decimal places. If you need blank filled 8 digits, then Right(" " & Format([numberfield], "#.00"), 8) should do the job (though it will truncate larger numbers without warning). John W. Vinson[MVP] |
#12
|
|||
|
|||
Access Query Right Align
Gary - The DataType is text for all fields. The field that needs to be
right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" text export subroutine for you. We are real busy here preparing to convert from ISBN's with 10 digits to new 13 digits, or else one should just have a routine that accepts --SpecID for tblSaveIMEXColumns (where one more field was added to it say "RightAlign" Yes/No, default 0) --recordset of data --path/filename of text file It's not hard to print a string you prepare yourself to a text file. Dim hFile As Long Dim strPath As String Dim strTextLine As String Dim rst As DAO.Recordset strPath = "C:\xxx.txt" strSQL = "SELECT ...." Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close "Donna" wrote: It still left-aligns the field. Here is what I put in the "field name" in the design view of the query: amt: Format([true-amt],"000000.00") It looks great all thru the export after I apply the export specification, but when the .txt file is created it is left aligned. "John Vinson" wrote: On Tue, 11 Jul 2006 09:54:01 -0700, Donna wrote: This export has to be in a specific format because it is going to be imported into the accounts payable system. Is there no way to right align the number field??? You can use the Format() function to convert the number to a text string. For example, an expression like ExpNum: Format([numberfield], "00000000.00") will convert the number to an 8 digit zero filled number with two decimal places. If you need blank filled 8 digits, then Right(" " & Format([numberfield], "#.00"), 8) should do the job (though it will truncate larger numbers without warning). John W. Vinson[MVP] |
#13
|
|||
|
|||
Access Query Right Align
start a new module
copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" text export subroutine for you. We are real busy here preparing to convert from ISBN's with 10 digits to new 13 digits, or else one should just have a routine that accepts --SpecID for tblSaveIMEXColumns (where one more field was added to it say "RightAlign" Yes/No, default 0) --recordset of data --path/filename of text file It's not hard to print a string you prepare yourself to a text file. Dim hFile As Long Dim strPath As String Dim strTextLine As String Dim rst As DAO.Recordset strPath = "C:\xxx.txt" strSQL = "SELECT ...." Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close "Donna" wrote: It still left-aligns the field. Here is what I put in the "field name" in the design view of the query: amt: Format([true-amt],"000000.00") It looks great all thru the export after I apply the export specification, but when the .txt file is created it is left aligned. "John Vinson" wrote: On Tue, 11 Jul 2006 09:54:01 -0700, Donna wrote: This export has to be in a specific format because it is going to be imported into the accounts payable system. Is there no way to right align the number field??? You can use the Format() function to convert the number to a text string. For example, an expression like ExpNum: Format([numberfield], "00000000.00") will convert the number to an 8 digit zero filled number with two decimal places. If you need blank filled 8 digits, then Right(" " & Format([numberfield], "#.00"), 8) should do the job (though it will truncate larger numbers without warning). John W. Vinson[MVP] |
#14
|
|||
|
|||
Access Query Right Align
Thanks for the code!!!! I know you are busy, so if you can't help me, please
let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" text export subroutine for you. We are real busy here preparing to convert from ISBN's with 10 digits to new 13 digits, or else one should just have a routine that accepts --SpecID for tblSaveIMEXColumns (where one more field was added to it say "RightAlign" Yes/No, default 0) --recordset of data --path/filename of text file It's not hard to print a string you prepare yourself to a text file. Dim hFile As Long Dim strPath As String Dim strTextLine As String Dim rst As DAO.Recordset strPath = "C:\xxx.txt" strSQL = "SELECT ...." Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With |
#15
|
|||
|
|||
Access Query Right Align
this is all untested code...
plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" text export subroutine for you. We are real busy here preparing to convert from ISBN's with 10 digits to new 13 digits, or else one should just have a routine that accepts --SpecID for tblSaveIMEXColumns (where one more field was added to it say "RightAlign" Yes/No, default 0) --recordset of data --path/filename of text file It's not hard to print a string you prepare yourself to a text file. Dim hFile As Long Dim strPath As String Dim strTextLine As String Dim rst As DAO.Recordset strPath = "C:\xxx.txt" strSQL = "SELECT ...." Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With |
#16
|
|||
|
|||
Access Query Right Align
I typed in fExpAP_Pmt and hit enter and I got this error:
Compile error: Ambiguous name detected: fExpAP_Pmt "Gary Walter" wrote: this is all untested code... plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" |
#17
|
|||
|
|||
Access Query Right Align
did you cut the attempt earlier
to run the function in the wrong window? "Donna" wrote in message ... I typed in fExpAP_Pmt and hit enter and I got this error: Compile error: Ambiguous name detected: fExpAP_Pmt "Gary Walter" wrote: this is all untested code... plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, I.FileType, I.SpecID, I.SpecName, I.SpecType, I.StartRow, I.TextDelim, I.TimeDelim INTO tblSaveIMEX FROM MSysImexSpecs AS I; If you copy specs for your export from tblSaveIMEXColumns here in readable form (actually only need following fields): DataType FieldName Start Width and identify field you want right-aligned I will try to pump out a "quick-and-dirty" |
#18
|
|||
|
|||
Access Query Right Align
I deleted the original modTest and created a new one. When I ran it, this is
the message I received: 3265 Item not found in this collection. "Gary Walter" wrote: did you cut the attempt earlier to run the function in the wrong window? "Donna" wrote in message ... I typed in fExpAP_Pmt and hit enter and I got this error: Compile error: Ambiguous name detected: fExpAP_Pmt "Gary Walter" wrote: this is all untested code... plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, |
#19
|
|||
|
|||
Access Query Right Align
I never use spaces in query/table names,
so forgive me if I forget to bracket.. change following code line (put brackets around query name) Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) to Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _ dbOpenSnapshot) that is...if query name is for sure "AP Payment Export" save your code and compile again if you put a question mark in front of the function when you type in Immediate Window, you will get "True" if it completes properly, i.e., in Immediate Window, type ?fExpAP_Pmt hopefully, you will get True w/change "Donna" wrote: I deleted the original modTest and created a new one. When I ran it, this is the message I received: 3265 Item not found in this collection. "Gary Walter" wrote: did you cut the attempt earlier to run the function in the wrong window? "Donna" wrote in message ... I typed in fExpAP_Pmt and hit enter and I got this error: Compile error: Ambiguous name detected: fExpAP_Pmt "Gary Walter" wrote: this is all untested code... plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 amt 46 9 FILLER1 55 1 case# 56 25 charge 81 4 FILLER5A 85 5 FREQ 90 2 FILLER10 92 10 "Gary Walter" wrote: Did you save as a "spec?" Then one of the "SpecID's" in the table created by following SQL will be what you have so far.... which I understand you as saying works great except for alignment of one field... SELECT M.Attributes, M.DataType, M.FieldName, M.IndexType, M.SkipColumn, M.SpecID, M.Start, M.Width INTO tblSaveIMEXColumns FROM MSysIMEXColumns AS M ORDER BY M.SpecID, M.Start; SELECT I.DateDelim, I.DateFourDigitYear, I.DateLeadingZeros, I.DateOrder, I.DecimalPoint, I.FieldSeparator, |
#20
|
|||
|
|||
Access Query Right Align
Forgive you..???? For what, helping!! I am grateful for your help. I changed
the code, and I copied the name from the query and pasted it into the code to make sure I got it exactly correct. Here is what that line says: Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _ dbOpenSnapshot) When I ran it, I got this message: 3078 The Microsoft Jet database engine cannot find the input table or qury '[AP Payment Export]'. Make sure it exists and that its name is spelled correctly. "Gary Walter" wrote: I never use spaces in query/table names, so forgive me if I forget to bracket.. change following code line (put brackets around query name) Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) to Set rst = CurrentDb.OpenRecordset("[AP Payment Export]", _ dbOpenSnapshot) that is...if query name is for sure "AP Payment Export" save your code and compile again if you put a question mark in front of the function when you type in Immediate Window, you will get "True" if it completes properly, i.e., in Immediate Window, type ?fExpAP_Pmt hopefully, you will get True w/change "Donna" wrote: I deleted the original modTest and created a new one. When I ran it, this is the message I received: 3265 Item not found in this collection. "Gary Walter" wrote: did you cut the attempt earlier to run the function in the wrong window? "Donna" wrote in message ... I typed in fExpAP_Pmt and hit enter and I got this error: Compile error: Ambiguous name detected: fExpAP_Pmt "Gary Walter" wrote: this is all untested code... plus, quick-and-dirty.... 1) you can change "C:\xxx.txt" to a for-sure path and filename... be sure and save/compile again if it wasn't q&d, one would input this path/filename when call function, then, code would test if it exists, and if it does, ask user if wanted to overwrite it or cancel.... but I don't even know if what is there works, or if it works, does what you want. 2) in the window where you entered code for module, there usually is a "bottom window" with a title bar of "Immediate" if it is not there, then hit CTRL G keys simultaneously that's what they call the "Immediate Window" 3) if it works and does what you want (all off top of my head, so probably something wrong somewhere) you could create a macro to run it, or (recommended), put a command button on a form that runs it when clicked. "Donna" wrote: Thanks for the code!!!! I know you are busy, so if you can't help me, please let me know. I absolutely do not want to hinder you. I have a couple of questions: 1. "C:\xxx.txt" - is this where I input the location of where I want the txt tile to be exported? 2. I keyed in fExpAP_Pmt at the very bottom left hand corner of the page after I compiled, hit enter and nothing happened. 3. Can I set up a Macro to automatically run this module? "Gary Walter" wrote: start a new module copy and paste this function into the new module save module as "modTest" in top menu, click on Compile.. from "Debug" drop-down if all above went well, test it by: in Immediate Window at bottom of window, type fExpAP_Pmt and hit ENTER key '***start code**** Option Explicit Public Function fExpAP_Pmt() As Boolean On Error GoTo Err_fExpAP_Pmt Dim strPath As String Dim rst As DAO.Recordset Dim hFile As Long Dim strTextLine As String Dim varCurFld As Variant strPath = "C:\xxx.txt" Set rst = CurrentDb.OpenRecordset("AP Payment Export", _ dbOpenSnapshot) With rst .MoveFirst 'First get a new file handle hFile = FreeFile 'Open the strPath to write the text Open strPath For Output As hFile Do While (Not .EOF) strTextLine = "" 'collect field values into strTextLine 'in order/width per IMEX Spec; 'left-align all but your one field 'ssn-name 1 25 varCurFld = Left(rst![ssn-name] & Space(25), 25) strTextLine = strTextLine & varCurFld 'date 26 6 varCurFld = Left(rst![Date] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER5 32 5 varCurFld = Left(rst!FILLERS & Space(5), 5) strTextLine = strTextLine & varCurFld 'v# 37 6 varCurFld = Left(rst![v#] & Space(6), 6) strTextLine = strTextLine & varCurFld 'FILLER3 43 3 varCurFld = Left(rst!FILLER3 & Space(3), 3) strTextLine = strTextLine & varCurFld 'amt 46 9 ---RIGHT ALIGN varCurFld = Right(Space(9) & rst!amt, 9) strTextLine = strTextLine & varCurFld 'FILLER1 55 1 varCurFld = Left(rst!FILLER1 & Space(1), 1) strTextLine = strTextLine & varCurFld 'case# 56 25 varCurFld = Left(rst![case#] & Space(25), 25) strTextLine = strTextLine & varCurFld 'charge 81 4 varCurFld = Left(rst!charge & Space(4), 4) strTextLine = strTextLine & varCurFld 'FILLER5A 85 5 varCurFld = Left(rst!FILLER5A & Space(5), 5) strTextLine = strTextLine & varCurFld 'FREQ 90 2 varCurFld = Left(rst!FREQ & Space(2), 2) strTextLine = strTextLine & varCurFld 'FILLER10 92 10 varCurFld = Left(rst!FILLER10 & Space(10), 10) strTextLine = strTextLine & varCurFld 'print the collected string to textfile Print #hFile, strTextLine 'get next record .MoveNext Loop End With Close hFile ' Close file. rst.Close fExpAP_Pmt = True Exit_fExpAP_Pmt: Set rst = Nothing Reset Exit Function Err_fExpAP_Pmt: MsgBox Err.Number & " " & Err.Description Resume Exit_fExpAP_Pmt End Function '***end code*** "Donna" wrote in message ... Gary - The DataType is text for all fields. The field that needs to be right-aligned is the "amt" field. I know this is asking a lot, but I really, really appreciate your help. "Gary Walter" wrote: missing 1 requested field please DataType FieldName Start Width this will not involve a schema.ini file.... this will read in field values for a record, then create *line* in code for that record, then write that line to a text file.... if that's too complicated for you, tell me now so I don't waste my time please... if not, it might also help me if you could copy here a few lines of the "badly aligned text file" thanks "Donna" wrote: Gary - here is the specs for the export specification. Everything I know about Access I taught myself, so all that 'language' you posted may as well be Greek. haha I appreciate your help. If I understand correctly, you can create a schema.ini file which I can save in the folder where the export is to be placed and everythime I export, the amount field will right align, right?? The name of the Query I am exporting is AP Payment Export if this matters. Field Name Start Width ssn-name 1 25 date 26 6 FILLER5 32 5 v# 37 6 FILLER3 43 3 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Last Post on the Topic: MS Access vs. Something Else | david epsom dot com dot au | General Discussion | 5 | April 20th, 2006 11:20 AM |
Access 2000 -vs- Access 2003? | Mark | General Discussion | 5 | November 30th, 2004 06:36 AM |
WORD XP mail-merge FAILS using ACCESS Query | SueMackay | Mailmerge | 1 | November 23rd, 2004 01:03 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |
Taher | Setting Up & Running Reports | 1 | August 31st, 2004 09:07 PM |