EDI file generate

M

MIchael Halliday

OK, I have seen a couple of posts that have danced areound this, but I
think I have some added hitches, so ANYONE who has worked this out
PLEASE help, I am already late for completing this development :(

I am trying to export data to an EDI format (810 invoice). the trick I
am running into is that there are multiple formats for the records in
the file. that is

there is a format A record , followed by multiple format B records,
followed by multiple format C records, ,,,, followed by a new Format A
record, etc.....
Each format is a series of fixed length fields.

SO, if they were all the same, i would just get them in a table, and
export the table to a text file, setting up the right lengths, a viola,
Bob's your uncle!!

But what I ahve done so far is create a report with no headers or
footer, with 2 sub reports, so I get the records in the right format,
in the right place, BUT when I export this report to a file, the fields
are not the right width (and seem to add spacing to separte the
columns). I am struggling with specifying field widths in inches,
(when I would prefer to specify it in characters). I am using Courier
font so there is not proportional font issues to contend with.

Anyone done this, formating this through the report designer seems to
be PAINFUL, perhaps there is a much easier way....I hope???

H E L P ! ! ! ?


Thanks
 
M

MIchael Halliday

yeah, that would be one approach, I could write the sql , and just do
massive concat's, then report on those queries...., but would it pad
the fileds with the appropriate spacies... probably not, hmmmm
 
M

MIchael Halliday

OK, so here is what I think I need ot do, for each record type I create
a query that will return the concatenated fields, but using the Left
and Space functions to ensure the necessary Space padding like:

SELECT [10 Records].ID, [10 Records].[Trading partner Code] & [10
Records].[Record ID] & [10 Records].[Invoice Date] &
Left(([10 Records].[Invoice Number] & Space(20)),20)
& left (( [10 Records].[PO NUmber] & space(20)), 20)
& left (( [10 Records].[sans Number] & space(10)) , 10)
&left (( [10 Records].[Terms basis Date Code] & space(2)), 2)
& left (( [10 Records].[Terms net Days] & space(3)), 3)
& left (( [10 Records].[Chargeback Number] & space(20)), 20)
AS Expr1
FROM [10 Records];

Where, for example, invoice NUmber should be 20 characters fixed
length.

Now I need a report that uses these queries instead of the actual
tables as input.

Dont know if this helps anyone, but I think it is going to work for me
 
Top