change datatype of a fieldname by code

[geert.van.ransbeeck]

Hello
I just imported a csv-document into an new access table.
Every fieldname has been correctly imported except for one. In this
field I need a text field instead of a number. The data is like
09078978979, 09077879000, etc... so it is logical he detects it as a
number leaving the first 0. For me it's important that the first 0
doesn't disappear. Text datatype would be ok. How can I preserve the
0, so how can I change this field into text by code.
thabks a lot for your suggestion.

kind regards
geert

 

[pietlinden]

Hello
I just imported a csv-document into an new access table.
Every fieldname has been correctly imported except for one. In this
field I need a text field instead of a number. The data is like
09078978979, 09077879000, etc... so it is logical he detects it as a
number leaving the first 0. For me it's important that the first 0
doesn't disappear. Text datatype would be ok. How can I preserve the
0, so how can I change this field into text by code.
thabks a lot for your suggestion.

kind regards
geert

If the fields in your data are consistent, why not just create a table
that you can import into, create an import specification which you use
in your TransferText command? Then since the table's structure is
constant, your problems should go away.

 

[John W. Vinson]

Hello
I just imported a csv-document into an new access table.
Every fieldname has been correctly imported except for one. In this
field I need a text field instead of a number. The data is like
09078978979, 09077879000, etc... so it is logical he detects it as a
number leaving the first 0. For me it's important that the first 0
doesn't disappear. Text datatype would be ok. How can I preserve the
0, so how can I change this field into text by code.
thabks a lot for your suggestion.

kind regards
geert

Your problems may be worse than you think: Long Integers are limited to 2^31,
two billion odd - and your numbers are bigger than that. What is the datatype
of the imported field?

Piet's suggestion is right on the mark - *link* to this CSV file and run an
Append query into a predefined table with the appropriate datatypes.

 

[geert.van.ransbeeck]

Your problems may be worse than you think: Long Integers are limited to 2^31,
two billion odd - and your numbers are bigger than that. What is the datatype
of the imported field?

Piet's suggestion is right on the mark - *link* to this CSV file and run an
Append query into a predefined table with the appropriate datatypes.

Thanks a lot but how can I append my lost 0 to this new table?

 

[geert.van.ransbeeck]

Your problems may be worse than you think: Long Integers are limited to 2^31,
two billion odd - and your numbers are bigger than that. What is the datatype
of the imported field?

Piet's suggestion is right on the mark - *link* to this CSV file and run an
Append query into a predefined table with the appropriate datatypes.

Thanks a lot but how can I append my lost 0 to this new table?

 

[John W. Vinson]

Thanks a lot but how can I append my lost 0 to this new table?

You can run an Update query updating the field to

Right("00000000000" & [fieldname], 11)

to append eleven zeros to the start of the field, and then take the last
eleven bytes of the result; this will append zero, one, two or however many
leading zeroes are needed.