Exporting in Flat File format - Please assist

[Guest]

I am creating an application that has to produce a flat file for export. This
I can do using the export wizard. However, I need the flat file to adhere to
a specific format as defined by the state in which I live. I have already
created my tables forms reports etc., I'm not sure now how to turn that data
into the required format. Example:

The user would enter an incident location as "Residence" in a form (combo
box). The state defines this as,

Field: 25 Bit: 108-109 Length: 2 Description: Incident location Coding:
01 or 1 = "Residence"

Another example:

Field: 30 Bit: 120 Length: 24 Description: Medical History
- 24 byte boolean values each Y or N for each byte for the following illness

Coding: "Y" in byte 4 = Diabetes

 

[Reinhart Stevens]

beste
adrie lustje
AUB.
groletje reinhartje

 

[SirPoonga]

What does field and bit mean?

It sounds like you need to make a function that goes through a
recordset based on a query using fixed width strings and/or the format
function.

 

[Guest]

Field and bit are titles for those required elements in the definitions
provided by the state. I am going to try a query method. Any specific tips?

 

[George Nicholson]

-Select the table/query you want to export in the database window (don't
open it)
-File>Export
-Select "Text File" as file type. Click OK
-The export text wizard starts
-Click "Advanced" in the wizard
- Select "Fixed width", not "Delimited"
- For each field, you should now be able to supply specifications regarding
size, data type, etc.
Your data should already contain the desired information, since you can't do
a lot in the way of manipulation beyond positioning.
The Start value of any given field should equal Start + Length of the
previous field. You can change the order of fields by manipulating Start
values but you can't overlap fields or leave gaps between fields.
[Field25/Location: Start = 108, Length: 2] Your Location field should
already contain 01 or 1 for residence. This simply allows you to control its
position in the exported file.
[Fields 26 to 29 in positions 110 to 129...]
[Field30/MedHistory: Start = 130, Length 24] Depending on the data on your
end, this might/might not be easier to treat as 24 1 char fields for each
illness. In a Fixed width file, I don't think there is any difference
between that and a single 24 char field where each char represents a
distinct value.
[Field 31: Start = 154....]
- Click "SaveAs" and you can give this set of specifications a name and save
it (within this db).
- Clicking "Specs" from this screen allows you to load a previously saved
set of specs (which can be simply applied or edited & resaved)
- Saved specification names can also be used by the VBA TransferText method
if/when you want to automate the export step (but you'd still need to create
the initial spec manually).

Unfortunately, the only way I know of to get a hardcopy of a FileSpec is to
use PrintScreen, 8 fields at a time. :-(

HTH,

 

[SirPoonga]

Am I the only one who does not have File/Export as an option when
looking at a query? Or is it Access 2K that doesn't have that option?

 

[George Nicholson]

My copy of 2k has it. So does XP/2002

(and you don't have to have the query open. Simply selected in the database
window is enough)

 

[Guest]

Thank you so much!

 

[SirPoonga]

When I have a query open in datasheet view it is greyed out. However,
I can right click on the query in the database window and have an
export option.

 

[Agaath]

When I have a query open in datasheet view it is greyed out. However,
I can right click on the query in the database window and have an
export option.