Report formatting problem help required

[TIDMAN]

Hi there all, I am in desperate need of assistance here as this is causing me some real stress at work as the deadline for this fix is getting closer by the day.
Here is the problem, our A2000 DB has an export function which generates a text file for orders the company that I work for decided that we need to go to a 20 digit field this was ok and I was able to fix this problem but they were not clear on other issues, the report that gets converted into the text file is where I believe the problem to be, there is an SO# that looks like this 14B155700 there are then a few spaces and 0ALLA0M I need the last 2 digits of the SO# to be placed right before 0ALLA0M looking like 000ALLA0M or 990ALLA0M what ever the last 2 digits are there are 2 parts of the report that require this change here is the code for line 1


="1" & IIf(Len([SO#])>=9,Left$([SO#],9),[SO#] & Space(9-Len([SO#]))) & "8" & "-" & "1" & Space(5) & "887" & Space(30) & Space(10) & IIf(Len([SO#])>=19,Left$([SO#],19),[SO#] & Space(19-Len([SO#]))) & "0" & "ALL" & "A" & "0" & "M" & Space(3)

ans Line 6

="5" & Format([RecordCount],"#,#00") & Format([Quantity],"000000") & Space(3) & [Units] & "887" & " " & " " & IIf(Len([SO#])>=19,Left$([SO#],19),[SO#] & Space(19-Len([SO#]))) & "0" & "ALL" & IIf(Len([PartDescription])>=24,Left$(StrConv([PartDescription],1),24),StrConv([PartDescription],1) & Space(24-Len([PartDescription]))) & Space(5) & IIf(Len([tbl_Parts.StyleNumber])>=20,Left$([tbl_Parts.StyleNumber],20),[tbl_Parts.StyleNumber] & Space(20-Len([tbl_Parts.StyleNumber]))) & Space(9)

I have inserted an image of the text file that is generated with some info and i have also inserted the report if someone would be willing to take a look and point me in the right direction please


I would be so very grateful for any assistance.

Thank you so very much in advance.

TIDMAN.......

 

[The_Doc_Man]

In general, when you want to generate something this complex, you are doing it wrong to start with. However, never let it be said that Access won't support some fancy footwork if you really need to do it.

Start with a query that has the individual parts that you want already present as pieces-parts. Then in a second-layer query (... yes, a query of a query... ), concatenate the individual fields. All of those "IIF" are going to eat your socks if you try to do the extraction and the concatenation as a monolithic operation.

Remember Caesar's Gallic Wars - divide and conquer! So break up the problem into parts small enough to manage. Then manage the parts. Then put the parts back together again. Take a layered approach. Sounds ugly (and probably IS ugly) - but if you have an ugly problem, why would you NOT expect an ugly solution?