Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Question Unanswered: Very Strange Fixed Width Report Needed... having trouble

    Hi folks... so i have been tasked with creating a fixed width (90 characters) bank transmission file. It has some strange requirements and maybe i am going about this the wrong way... so GUIDE ME OH GREAT ACCESS PEOPLE...

    Based on a date supplied by the user, the query pulls data for payments due. The first 2 rows of data are actually static (a report header, if you will) then on row 3 starts the actual bank records to be transferred.

    I have some text functions working in the query to give me the right amount of spaces to give me the bank number, name on the account, etc. Now... the requirement for the file is that on the actual bank records to be transferred, it needs to have a record counter (starting with 1)... now i never figured out how to make an "autonumberer" for a query that doesn't change the numbers as you scroll... so i decided to use the Running Sum in a report and export to a text file. Ok, well oddly enough, when i export to a text file, the name gets cut off... so i feel like i need to start from square one....

    does anyone have any brilliant suggestions? Besides burning the computer, of course?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by CompAnalyst View Post
    Hi folks... so i have been tasked with creating a fixed width (90 characters) bank transmission file. It has some strange requirements and maybe i am going about this the wrong way... so GUIDE ME OH GREAT ACCESS PEOPLE...

    Based on a date supplied by the user, the query pulls data for payments due. The first 2 rows of data are actually static (a report header, if you will) then on row 3 starts the actual bank records to be transferred.

    I have some text functions working in the query to give me the right amount of spaces to give me the bank number, name on the account, etc. Now... the requirement for the file is that on the actual bank records to be transferred, it needs to have a record counter (starting with 1)... now i never figured out how to make an "autonumberer" for a query that doesn't change the numbers as you scroll... so i decided to use the Running Sum in a report and export to a text file. Ok, well oddly enough, when i export to a text file, the name gets cut off... so i feel like i need to start from square one....

    does anyone have any brilliant suggestions? Besides burning the computer, of course?
    Sounds a lot like your previous post: HERE

    I have seen this type of thing a lots. Fixed width is not that uncommon with simple EDI. I have used it a lot when importing with COBOL. Which is the programming language commonly used by for accounting system.

    I still would recommend using VBA code like in my reply to the previous post.

    now i never figured out how to make an "autonumberer" for a query that doesn't change the numbers as you scroll...
    If you will use VBA code to do this, you can use a counter to easily create a "auto number" to do what you need.

    Code:
    Dim FH As Long
    Dim rs as DAO.recordset 
    Dim lngCounter as Long
    
    
    
        FH = FreeFile
        Open "c:\myfilename.txt" For Output As #FH
    
        rs = CurrentDB.OpenRecordSet("Query_Name")   
    
        lngCounter = 0
        Do Until rs.EOF 
            ' increment record counter
            lngCounter = lngCounter + 1
             
           Print #FH, lngCounter & " string to write to file"
    
    
            rs.MoveNext
        Loop
    
        Close FH
    Hope this helps ....
    Last edited by HiTechCoach; 05-17-10 at 01:20.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Nov 2003
    Location
    LA, CA
    Posts
    54

    Talking Mwahahhahaha I got it!!!

    Thanks for your assistance... here is the actual code to get the date from a form and output this file:

    Option Explicit
    Public Function Counter()


    Dim FH As Long
    Dim rs As DAO.Recordset
    Dim strOther As String
    Dim lngCounter As Long
    Dim Spacer As Long

    FH = FreeFile

    ' this allows the user to name the file and location from a form called_
    ' frmFixedWidthFileCreator
    Open Forms!frmFixedwidthfilecreator!ExportFolder & "\" & Forms!frmFixedwidthfilecreator!ExportFileName & ".txt" For Output As #FH

    'this filters the query based on the date the user inputs on the form
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryFixedWidthMain WHERE DateofPayment =#" & Forms!frmFixedwidthfilecreator!Text0 & "#")

    ' 1st line of file- static and required to have certain spaces and such"
    Print #FH, "176876487354545XXX0000 CompanyName"
    ' 2nd line of file- also static
    Print #FH, "XXXXXCompanyName XXX000007SSQ DEBIT 283748326423424 1"
    lngCounter = 0
    Do Until rs.EOF


    ' increment record counter
    lngCounter = lngCounter + 1
    strOther = rs![final]
    ' file must be only 94 characters long, therefore, need extra spaces if there are not 3 digits in the counter
    If lngCounter <= 9 Then
    Spacer = 16
    ElseIf lngCounter >= 10 And lngCounter <= 99 Then
    Spacer = 15
    ElseIf lngCounter > 99 Then
    Spacer = 14
    End If
    Print #FH, strOther; Spc(Spacer); lngCounter
    rs.MoveNext
    Loop

    Close FH
    End Function



    It might not be the cleanest way... BUT DANGIT.... IT WOOOOOOOOOOOOOOORKS... thank you thank you and i hope that other people can learn from my frustration!!!!!

    I am doing the happy dance as you read this!!!!!

  4. #4
    Join Date
    May 2010
    Posts
    601

    Thumbs up

    Quote Originally Posted by CompAnalyst View Post
    Thanks for your assistance... here is the actual code to get the date from a form and output this file:

    Option Explicit
    Public Function Counter()


    Dim FH As Long
    Dim rs As DAO.Recordset
    Dim strOther As String
    Dim lngCounter As Long
    Dim Spacer As Long

    FH = FreeFile

    ' this allows the user to name the file and location from a form called_
    ' frmFixedWidthFileCreator
    Open Forms!frmFixedwidthfilecreator!ExportFolder & "\" & Forms!frmFixedwidthfilecreator!ExportFileName & ".txt" For Output As #FH

    'this filters the query based on the date the user inputs on the form
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryFixedWidthMain WHERE DateofPayment =#" & Forms!frmFixedwidthfilecreator!Text0 & "#")

    ' 1st line of file- static and required to have certain spaces and such"
    Print #FH, "176876487354545XXX0000 CompanyName"
    ' 2nd line of file- also static
    Print #FH, "XXXXXCompanyName XXX000007SSQ DEBIT 283748326423424 1"
    lngCounter = 0
    Do Until rs.EOF


    ' increment record counter
    lngCounter = lngCounter + 1
    strOther = rs![final]
    ' file must be only 94 characters long, therefore, need extra spaces if there are not 3 digits in the counter
    If lngCounter <= 9 Then
    Spacer = 16
    ElseIf lngCounter >= 10 And lngCounter <= 99 Then
    Spacer = 15
    ElseIf lngCounter > 99 Then
    Spacer = 14
    End If
    Print #FH, strOther; Spc(Spacer); lngCounter
    rs.MoveNext
    Loop

    Close FH
    End Function



    It might not be the cleanest way... BUT DANGIT.... IT WOOOOOOOOOOOOOOORKS... thank you thank you and i hope that other people can learn from my frustration!!!!!

    I am doing the happy dance as you read this!!!!!
    Great Job!

    Only one thing I would suggested. Try using the Format() to make things a littler easier,

    Something like this:

    Code:
    Option Explicit
    Public Function Counter()
    
    
    Dim FH As Long
    Dim rs As DAO.Recordset
    Dim strOther As String
    Dim lngCounter As Long
    Dim Spacer As Long
    
    FH = FreeFile
    
    ' this allows the user to name the file and location from a form called_
    ' frmFixedWidthFileCreator
        Open Forms!frmFixedwidthfilecreator!ExportFolder & "\" & Forms!frmFixedwidthfilecreator!ExportFileName & ".txt" For Output As #FH
    
    'this filters the query based on the date the user inputs on the form
        Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryFixedWidthMain WHERE DateofPayment =#" & Forms!frmFixedwidthfilecreator!Text0 & "#")
        
    ' 1st line of file- static and required to have certain spaces and such"
    Print #FH, "176876487354545XXX0000                CompanyName"
    ' 2nd line of file- also static
        Print #FH, "XXXXXCompanyName                          XXX000007SSQ  DEBIT   283748326423424                  1"
        lngCounter = 0
        Do Until rs.EOF
    
    
            ' increment record counter
            lngCounter = lngCounter + 1
        strOther = rs![final]
    
        ' file must be only 94 characters long, therefore, format  counter  to 3 digits
    
        Print #FH, strOther; String(14," "); Format(lngCounter,"000")
            rs.MoveNext
        Loop
    
        Close FH
    End Function

    Well done!
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •