CSV file from HELL

[prostheticHead]

Hopfully this is the right area for this

i have a big problem i need to be able to make a CSV file from the information on a form which uses sub forms and all sorts and also mustbe layed out exactly the same as a csv file that a few major insurance companys use

complecated hay!

the only way i can see it happening is with lots of VBA code and writing to a text file exactly how i want it.
but theres so much i dont know about VBA

major wall right now is that i have no idea how to pull the info i need out of the sub forms so that i can put it in the csv

Any Help on how to go about this would be exelent! i dont even know if this is the right move. mostly geting the correct info out of the sub forms is the main flaw in my plan!



Thanks HEAPS!


ProstheticHead

I have also attached some images so ya can see the form im working with

 

[alastair69]

Is it all the data you need to pull into an csv or just a selection.

 

[prostheticHead]

no not everything gose into the file some things are there fo usability so that the user can dubble check there dealing with the right file. such as the address details..

 

[alastair69]

I will need to know wot info you need to write to the cvs.

 

[prostheticHead]

the file needs to look like this

TAX INVOICE,(Invoice Date),(Invoice No),(Date Completed),(ABN),(Suplyer Name),(PO Number),(Policy Number),(Claim No.),(Insurance company),(Total Excluding GST),(item Description),(Qty),(item Code)

a row of that for every item

then a final row at the end which has amost exactly the same details but it has the Excess as a item

 

[alastair69]

will have a look at it for you

 

[prostheticHead]

Like this

For Each item!
TAX INVOICE,(Invoice Date),(Invoice Number),(Date Completed),(Supplier ABN),(Supplier Name),(PO No),(Policy No),(Claim No),(company Name),(Total inc GST),1,(GST),GEN,(Description),(QTY),(itemCode),Nill,Nill,Nill,Nill


On the last Line
TAX INVOICE,(Invoice Date),(Invoice Number),(Date Completed),(Supplier ABN),(Supplier Name),(PO No),(Policy No),(Claim No),(company Name),(Excess),0,0,GEN,Excess,1,XS,Nill,Nill,Nill,Nill

 

[prostheticHead]

Thanks heaps!

 

[Pat Hartman]

I like your form.

 

[prostheticHead]

Thanks!

 

[prostheticHead]

ok well i got the CSV file being writen but i need to know if its prosible to convert the date from a format like 30/12/2004 to a format like 20041230

anyone have any ideas?

thanks heaps

 

[prostheticHead]

ive been playing with the format and formatDateTime methods but have had no luck it just keeps comeing out as 07/01/2005 not 20050107 dam this CSV and its weird requirments!

 

[Pat Hartman]

You'll need to export a query rather than a table. Use the Format() function.

Select Format(YourDate, "yyyymmdd") As formatteddate, .....