[AccessD] Question on Creating a Flat File with Access 2007

That rigns a bell.   Financial data export to an Import batch File?

Definitiely a case for stepping through a recordset and and bulding the strings
something like this (from a Bank Transfer Batch file creation):     

With rsBankTfrs
...
outstr = "13" & _
       Right$("00" & LTrim$(Str$(!Bank)), 2) & _
       IIf(!Bank = 1, "0000", "0060") & _
       "0" &  _
       IIf(!Bank = 1, Right$("000000" & Left$(!Account, Len(!Account) - 2), 6), "991158") & _ 
       "0" & _
        IIf(!Bank = 1, Right$(!Account, 2), "08")  & _
        "0" & "_
        52" &  _
        Format(!Amount * 100, "0000000000")  & _
        Left$(!Payee & Space$(20), 20) & _
        Space$(12) & _
        Space$(12) & _
        Space$(12) & _
        Left$(IIf(!Bank = 4, Right$(!BSB, 3) & "-" & !Account, !Account)  & Space$(12), 12) & _
        Space$(60) & _
        "0"
Print #ff outstring
....

-- 
Stuart

On 4 Jun 2011 at 8:47, Brad Marks wrote:

> Bill,
> 
> The file specifications (from the other firm) call for 7 different
> types of records.
> 
> For example...
> 
> "00" record with 8 fields - total length 80 bytes
> "10" record with 27 fields - total length - 674 bytes
> "90" records with 17 fields - total length 336 bytes
> 
> I believe I now have a good way to do this in Access 2007 VBA (with a
> little help from others).
> 
> After 32 years of working with IBM Mainframe COBOL, etc, I might be
> making this more complicated than it needs to be  :-)   
> 
> Brad
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com on behalf of William Benson
> Sent: Fri 6/3/2011 10:38 PM To: Access Developers discussion and
> problem solving Subject: Re: [AccessD] Question on Creating a Flat
> File with Access 2007
> 
> I dont understand what is meant by different record types? I know what
> field types are but what is a record type?
> 
> Would you consider using queries to create a local table with. The
> desired result then export that then import it again into another
> table with an import specification ... and compare the two tables. It
> makes double work but computers arent paid by the hour anyway. It will
> prove beyond any doubt they got the data you think they got plus you
> have a record of what you sent.
> 
> Bill Benson
> Owner
> VBACreations, LLC
> On Jun 3, 2011 2:36 PM, "Brad Marks" <BradM at blackforestltd.com> wrote:
> > Gary, Robert, Darrell, > > Thanks for the help, I appreciate it. > >
> Brad > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~ > > > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com >
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell
> Burns > Sent: Thursday, June 02, 2011 4:59 PM > To: 'Access Developers
> discussion and problem solving' > Subject: Re: [AccessD] Question on
> Creating a Flat File with Access 2007 > > Brad, > Another option would
> be to just create & save a query that converts the > numeric values to
> strings; eg, using CSTR(), then right-click and export > the > query
> as a text or Excel file. That works if there aren't OLE, blob, or >
> image types. > HTH, > Darrell > > -----Original Message----- > From:
> accessd-bounces at databaseadvisors.com >
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos >
> Sent: Thursday, June 02, 2011 7:28 AM > To: Access Developers
> discussion and problem solving > Subject: Re: [AccessD] Question on
> Creating a Flat File with Access 2007 > > For multiple record types
> and lengths, I would use File IO to write the > file. > > Longtime
> AccessD member Seth Galitzer has example code here > >
> http://seth.galitzer.net/node/11 > > Look for "File IO in VB" > > GK >
> > On Wed, Jun 1, 2011 at 4:06 PM, Brad Marks
> <BradM at blackforestltd.com> > wrote: >> We need to create a sequential
> file to feed to another firm according > to >> their specifications.
> >> >> The specs call for several different "Record Types". >> >> Each
> Record Type has a different length. >> >> The source data resides in
> tables that we are currently reading into >> Access via ODBC. >> >>
> What is the best way to create this output file with Access? >> >>
> Thanks, >> Brad >> >> -- >> AccessD mailing list >>
> AccessD at databaseadvisors.com >>
> http://databaseadvisors.com/mailman/listinfo/accessd >> Website:
> http://www.databaseadvisors.com >> > > > > -- > Gary Kjos >
> garykjos at gmail.com > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com > > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com > > -- > This message has been scanned
> for viruses and > dangerous content by MailScanner, and is > believed
> to be clean. > > > -- > AccessD mailing list >
> AccessD at databaseadvisors.com >
> http://databaseadvisors.com/mailman/listinfo/accessd > Website:
> http://www.databaseadvisors.com -- AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
> 
> -- 
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
> 
> 
>