Exporting Delimited Files

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 wrote:
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


Nz(NullColumn,Space(2)) & FiveCharacterColumn

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Stryker22150" wrote in message
oups.com...
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

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.