Exporting Delimited Files

  • Thread starter Thread starter Stryker22150
  • Start date Start date
S

Stryker22150

When exporting a query in delimited format with null values the data is
pushed to the left. I need to be able to maintain consistency. I have
discovered I can use an IIF comb with the Space function to maintain
consistency. I am having a problem with concatenating 2 fields +
including the IIF and the Space functions.


ie... One of the fields may be null. It is only 2 characters. The other
field it is concatenated with is not null and is 5 chaacters. Therefore
I need to include 2 spaces for the fields that are null.

The tables are linked.

TIA
 
Stryker22150 said:
When exporting a query in delimited format with null values the data is
pushed to the left. I need to be able to maintain consistency. I have
discovered I can use an IIF comb with the Space function to maintain
consistency. I am having a problem with concatenating 2 fields +
including the IIF and the Space functions.


ie... One of the fields may be null. It is only 2 characters. The other
field it is concatenated with is not null and is 5 chaacters. Therefore
I need to include 2 spaces for the fields that are null.

The tables are linked.

TIA
Click to expand...

Nz(NullColumn,Space(2)) & FiveCharacterColumn
 
<picky>
Sounds like you're talking about fixed-width, not delimited. If it were
delimited, you wouldn't care how many spaces it took: you'd have your
delimiter to indicate that the next field is starting.
</picky>

Looks like you've got a workable answer, though.
 
MG

Thanks for the quick response.

I have tried using the NZ function as suggested. Nz([field 2],Space(2))
& [table.field2] & [table.field 2]

This leaves the fields where "field 2" has some nulls untouched and
takes the first two characters and concatenates to the end of the
string. I now have a 9 sting and 5 string field. The field needs to be
7 characters.

I need a piped delimited file.

Doug;

If you could please explain why delimited file output is pushed to the
left when you export the file when a data field is null. This really
hashes the data. I have been able to generate a delimited piped file by
including Spaces for consistency. I am on the last one that is
concatentating two fields. Therefore I am hoping for a combination that
will fill the nulls with the required spaces.

In a Fixed width file I would hopefully have the file spec to ensure it
was in the same place every pull.

Thanks to all
 
Please disregard. When I brought it back into Access it fell into place
albeit, the extra characters included <g>

When you look at the Text file the columns are not aligned. I guess it
is like cooking a steak by appearance alone? <g>

Thanks again.
 
Back
Top