Help: docmd.TransferText formatting question

  • Thread starter Thread starter A Coyle
  • Start date Start date
A

A Coyle

Hi
I am exporting some records to csv file using the docmd.TransferText command
Some of the records have empty text fields.

When I export these records they are comma deliminated, the text values have
quotes around them, but a record without any text just gets the commas
eg
"record1", 10, 20, 30, "textField", 40
"record2", 50, 60, 70, , 80

I need the EMPTY text field to be quoted as well. For example I would like
the above to become:
"record1", 10, 20, 30, "textField", 40
"record2", 50, 60, 70, "", 80

Please help.

Thanks in advance

regards
A
 
Reason this is occurring is because the "no text" value likely actually is a
Null value, not an empty string.

Easiest way to do this is to use a query as the source for the records, and
substitute a calculated field for the original field. This calculated field
then uses the Nz function to convert a Null to an empty string.

NewField: Nz([FieldName], "")
 
Wicked, thanks

regards
A

Ken Snell said:
Reason this is occurring is because the "no text" value likely actually is a
Null value, not an empty string.

Easiest way to do this is to use a query as the source for the records, and
substitute a calculated field for the original field. This calculated field
then uses the Nz function to convert a Null to an empty string.

NewField: Nz([FieldName], "")

--
Ken Snell
<MS ACCESS MVP>

A Coyle said:
Hi
I am exporting some records to csv file using the docmd.TransferText command
Some of the records have empty text fields.

When I export these records they are comma deliminated, the text values have
quotes around them, but a record without any text just gets the commas
eg
"record1", 10, 20, 30, "textField", 40
"record2", 50, 60, 70, , 80

I need the EMPTY text field to be quoted as well. For example I would like
the above to become:
"record1", 10, 20, 30, "textField", 40
"record2", 50, 60, 70, "", 80

Please help.

Thanks in advance

regards
A
Click to expand...
Click to expand...
 
From the table that you are exporting the records, set all
the TEXT fields to ALLOW ZERO LENGTH = YES and set the
REQUIRED field to YES.

When the user presses the SPACEBAR in these fields a zero-
length string is entered ("").

If you cannot count on the user to press the SPACEBAR in
the field you can create a UPDATE query. In the UPDATE
query your criteria would be all the TEXT fields with NULL
values. And you would update the NULL values to "".

This would force zero-length strings to be entered in all
the fields that do not have values and then when you
export you will have what you want.
 
Back
Top