Delimited & Fixed-length

[Guest]

Hello,

I wish to export my table contents to a txt file where the Field Delimiter
should be semi-colon ( and the Text Qualifier should be a double-quote (")
but where the field's lenght must be preserved.
ex: txt file should look like :
"John.....";"Europe";"Test......"
"Tom......";"USA...";"Test again"
(the dots replace the space for readability reason)

It looks like Access only allows one or the other, but not both of the
methods.
Is there a way to achieve this ?

Thank you in advance for any help,
Nicodemus

 

[Guest]

hello Jerry,

I was afraid I had to use this kind of solution, my table(s) contain(s) 62
fields...!!
Anyways, thank you very much for your answer.
Nicodemus

Create a query out of the table. For each field in the query, use the Space
function to pad out spaces.

FirstNameSpaces: [FirstName] & String(20-Len([FirstName])," ")

The above will put in the FirstName data then added spaces up to 20
characters total. For example you have "Jim", then 20 spaces minus the lenght
of "Jim" which is three.

Make sure that the size of the string does not ever exceed the number of
characters for the total length of the string. The String function does not
like negative numbers!

Also the String function doesn't like nulls so every field needs something
in it. You could add the NZ or and IIF to do something if a null is found.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I wish to export my table contents to a txt file where the Field Delimiter
should be semi-colon ( and the Text Qualifier should be a double-quote (")
but where the field's lenght must be preserved.
ex: txt file should look like :
"John.....";"Europe";"Test......"
"Tom......";"USA...";"Test again"
(the dots replace the space for readability reason)

It looks like Access only allows one or the other, but not both of the
methods.
Is there a way to achieve this ?

Thank you in advance for any help,
Nicodemus

 

[Guest]

Create a query out of the table. For each field in the query, use the Space
function to pad out spaces.

FirstNameSpaces: [FirstName] & String(20-Len([FirstName])," ")

The above will put in the FirstName data then added spaces up to 20
characters total. For example you have "Jim", then 20 spaces minus the lenght
of "Jim" which is three.

Make sure that the size of the string does not ever exceed the number of
characters for the total length of the string. The String function does not
like negative numbers!

Also the String function doesn't like nulls so every field needs something
in it. You could add the NZ or and IIF to do something if a null is found.