EDI file generate

  • Thread starter Thread starter MIchael Halliday
  • Start date Start date
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
 
MIchael said:
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
Click to expand...

I dabbled in working between an EDI format (835 remittance advice) and
Access and I came to the same conclusion -- it's painful.

Maybe something you can try in your report is to lay out empty text
boxes where you need them and populate them with concatenated data,
spaced appropriately.
 
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
 
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
 
Back
Top