Enter a carriage return in a fixed width file 1

[Shairal]

Hi everyone,
I need to export a fixed width file from Access 2002 (or 2007), which I know how to do in a query. However my file layout requires two carriage returns in the middle of the file – so it will read something like:

H ABCDEF GHIJKL
a xxxxx xxxxxxxxxx xxxxxxxxx
b xxxxxxxx xxxxxxxxxxxx xxxxxxxx

How do I include a carriage return when I export? I have seen something about entering a Char(13) or Char(10), but don't understand how to include them in my file.

Thanks in advance for all your help!

 

[RobertT687]

One way to include the carriage return would be:
Assume your field name is myInputField containing
"H ABCDEF GHIJKLa xxxxx xxxxxxxxxx xxxxxxxxxb xxxxxxxx xxxxxxxxxxxx xxxxxxxx".
In a query grid you can specify:

myOutputField:Left$(myInputField,1,15) & vbCrLf & Mid$(myInputField,16,28) & vbCrLf & Mid$(myInputField,44,32)

vbCrLf is VBA shorthand for carriage return [Chr(13)] plus line feed [Chr(10)].

 

[dhookom]

Keep in mind, you can't use vbCrLf (or other VBA constants) in a query window. They only work in VBA. You need to use Chr(13) & Chr(10) in control sources and queries.

Duane
Hook'D on Access
MS Access MVP

 

[Shairal]

Sorry, I guess I don't understand. I loaded my file so it looks like this - one field named myInputField:
"H ABCDEF GHIJKLa xxxxx xxxxxxxxxx xxxxxxxxxb xxxxxxxx xxxxxxxxxxxx xxxxxxxx"

And in a query window I entered:
myOutputField:Left$(myInputField,1,15) & Chr(13) & Chr(10) & Mid$(myInputField,16,28) & Chr(13) & Chr(10) & Mid$(myInputField,44,32)

And I'm obviously doing something wrong, because it doesn't work. Sorry, can you please provide more assistance?

 

[Shairal]

Oh, I got! (duhh!!)
Thank you so much for all your help!