Access Fixed Width export formatting issue

[mrushton]

Hi I am having alot of trouble with an urgent Access export and am wondering if someone can help.

I have some data I need to export in a fixed width format
with dates as: YYYYMMDDhhmm
and currency as: 00000000000;10000000000
(where the last two digits are the decimals, and negatives start with 1)

I have the following fields setup in the advanced export spec:

[tab] Field Name Data Type Start Width
[tab] REC_TYPE Text 1 1
[tab] INTERMEDIARYSHOPPERID Text 2 64
[tab] INTERMEDIARYTXID Text 66 20
[tab] ADVERTISERID Text 86 10
[tab] CLICKDATETIME Text 96 12
[tab] INTERMEDIARYTXDATETIME Text 108 12
[tab] ADVERTISERTXDATETIME Text 120 12
[tab] ACTIONID Text 132 2
[tab] ADVERTISERTXID Text 134 20
[tab] EVENTID Text 154 10
[tab] ADVERTISERLINKID Text 164 10
[tab] TXAMOUNT Text 174 11
[tab] COMMISSIONAMOUNT Text 185 11

I can format the date in the query correctly but I lose the formatting when I export because the standard export function doesn't like not having separators etc. I can export a report ok but this doesn't have the fixed width I require.

I have changed the "type" settings in the advanced window and setup the fixed width details but to no avail...I think the only way this will work will be writing it in VB. However I am not up on Access VB as yet. Can anyone help?? I have been tearing my hair out.

Thanks
B-(

 

[2Grandpa]

To export dates to a fixed lenght file, I wrote query with this as the field definition:

ActualClosedDateStr: IIf(IsDate([ACTUAL CLOSE DATE])=False,"00000000",Right(Str(Year([ACTUAL CLOSE DATE])),4)) & IIf(Month([ACTUAL CLOSE DATE])<10,'0' & Right(Str(Month([ACTUAL CLOSE DATE])),1),Right(Str(Month([ACTUAL CLOSE DATE])),2)) & IIf(Day([ACTUAL CLOSE DATE])<10,'0' & Right(Str(Day([ACTUAL CLOSE DATE])),1),Right(Str(Day([ACTUAL CLOSE DATE])),2))

You would have to add hours and minutes,