Formatting Query

[Novice2000]

Hello,
I need to export data as straight text not delimited in any way. There are
strict requirements for trhe lengths and orientation of the fields I need to
export for instance 11 characters left justified.

I was told that either I can output to the .txt file type from Access by
building a table first, where the fields are text and the lengths match
requirements and then export from it.

Or I can build a formatting query and export from that. The formatting
query to use the built-in formatting functions within Access to match the
required layout.

Can anyone tell me how to build a formatting query?

 

[Stefan Hoffmann]

hi,

Can anyone tell me how to build a formatting query?

Take a look at

http://office.microsoft.com/en-us/access/HA100069051033.aspx

Take also a closer look at the export specifications section.


If this is not sufficent for creating your text files, you may consider
exporting the data XML and using XSLT for creating your text file or
using a VBA method to export it.

Both tasks needs the appropriate development skills. But it can be done
and it can be learned.


mfG
--> stefan <--

 

[Tom van Stiphout]

On Fri, 9 Oct 2009 06:25:03 -0700, Novice2000

I would take the Query route, and combine it with the Export features
that Access already has built-in. It can do fixed-width exports, but
does not have support for justification. So you write that yourself.
Something like (off the top of my head):
Public Function LeftJustify(ByVal s As String, ByVal l As Integer)
LeftJustify = Left$(s, l) & Space$(IIf(l > Len(s), l - Len(s), 0))
End Function
Write similar functions for other formatting needs you may have.

Then you call this function from your query:
select LeftJustify(myField, 12), ...other fields...
from myTable

-Tom.
Microsoft Access MVP