Wizzard, Append & Format a fixed length file with-in Access?

[advpay]

I have a query on a table (that has only one field) that strips out specific rows that I need to process...<br>an example of one of these that I am processing is:<br><br>&nbsp;Query (specific row)<br><br>qryDetailedHeaderLayout&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>WholeField<br>30361036000004032229000003795000003795<br><br>I have a format setup in the wizzard to import this into&nbsp;&nbsp;<br>its 6 fields. (Detail Header Layout)<br><br>My question is how do you use import wizzard to append and format this into a new table as this is based on a query?<br><br>I know I can take my query and append it to a new table...but I still can't figure out how to reformat it with-in access into a new table?<br>

 

[famousb]

if i'm following your question, you want to know how to append the data into a new table with 6 fields, when the original data is coming from a table with 1 field?<br><br>therefore, the append query isn't working for you since it will just append to one field?<br><br>try the DoCmd.TransferText command in Visual Basic, and use the import wizzard that you created as the &quot;Specification&quot;.&nbsp;&nbsp;You shoul dbe able to use the acImport function, and just designate the db as both the place of origin and destination.<br><br> <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[advpay]

It's telling me that .... Step2tblimpBankFileFH.txt can't be found?<br><br>I am doing something wrong...eeks<br><br>I go into Macros:<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Action<br>TransferText<br><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Action Arguments<br>Transfer Type:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Import Fixed Width<br>Specification Name:&nbsp;&nbsp;File Header Layout<br>Table Name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Step2tblimpBankFileFH<br>File Name:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Step2tblimpBankFileFH&nbsp;&nbsp;<br><br>Microsoft Jet database can't find<br>&nbsp;Step2tblimpBankFileFH.txt&nbsp;&nbsp;<br><br>Not quite sure what i'm doing wrong?<br>

 

[famousb]

ok, i'm assuming the wizard you set up was named &quot;File Header Layout&quot; and you selected in the drop down box in the macro.<br><br>I'm also assuming that &quot;Step2tblimpBankFileFH&quot; is the name of the original table that contains the data you want to import?<br><br>But i think i led you slightly astray, the TransferText command only works for text files (which technically you could export it to a text file, then reimport it using this method, but that's kind of round about). this is why you're getting a message saying that the computer can't find file&nbsp;&nbsp;Step2tblimpBankFileFH.txt. <br><br>i'm trying to imagine other methods of seperating one field into six, without exporting it as a text file and then reimporting it, but i am drawing a blank as to what else could be done. <p>Brian Famous<br><a href=mailto:bfamous@ncdoi.net>bfamous@ncdoi.net</a><br><a href= > </a><br>

 

[advpay]

Ok i've changed my question alittle.<br><br>I have a table with one field and one row in it.<br><br>30361036000004032229000003795000003795<br><br>This needs to go though an import wizzard to be reimported into a new table with 6 columns.<br><br>Question...how is this done?<br><br>1. Both these tables are in the same database; can you import a table through the Access wizzard into a new table and use the wizzard to format it into 6 different columns with-in the same Database?<br><br>Thanks for all your help...<br><br>I'm stuck!<br><br><br><br><br><br>

 

[NightOwl]

I believe that you would be better to append the records using the mid() command on the data in a query update <br><br>i.e the first column would be mid([Field Name],1,6) if the field started at position&nbsp;&nbsp;1 and finished at position 6. Repeat for each field

 

[advpay]

does this only work with .txt?

 

[advpay]

I have this:<br><br>INSERT INTO tblHistDetailRecordLayout ( RecordTypeCode )<br>SELECT Step2tblimpBankFileDL.WholeFile<br>FROM Step2tblimpBankFileDL<br>WHERE (((Step2tblimpBankFileDL.WholeFile)=Mid$(&quot;WholeFile&quot;,1,1)));<br><br>and I am getting an undefined function 'Mid$' in expression<br><br>Thanks for the help...<br>

 

[advpay]

please help...

 

[kathryn]

Well, I'm new to this site, so I'll throw in my two cents' worth.&nbsp;&nbsp;<br><br>I would not use an append query to start, I would use a make table query.&nbsp;&nbsp;Use the query desig view and for the first column on the grid, enter something like:<br><br>FirstField:Mid([WholeFile],1,6)<br><br>In the second column:<br><br>SecondField:Mid([WholeFile],7,12)<br><br>etcetera....<br><br>The sample you gave above used quotes around WholeFile, which I am assuming is your field name.&nbsp;&nbsp;You need brackets.<br><br>If you still get the undefined function error, you have come upon one of the worst, IMHO, bugs in Access. While you have a module window open, go to Tools....References and see if you have any References that are missing.&nbsp;&nbsp;If you do, reset them and that should fix it.<br><br>HTH,<br>Kathryn