1

Topic: Saving of initial formatting of cells at import of the table from Excel in Access

All greetings! I need to import the table an ex-fur-tree in .
I do it by the following command

SELECT * INTO newtable23
FROM (SELECT CCD_0D_02, CCD_02_01, CCD_02_02, CCD_02_03, CCD_02_04, CCD_02_05, CCD_02_06, CCD_02_07, CCD_02_08, CCD_02_09, CCD_02_10;
CCD_08_01, CCD_08_02, CCD_08_03, CCD_08_04, CCD_08_05, CCD_08_06, CCD_08_07, CCD_08_08, CCD_08_09, CCD_08_10,
CCD_09_01, CCD_09_02, CCD_09_03
FROM 2014
WHERE CCD_33_01_ between "7304000000" and "7306999999")

This code works, but in the created table access in some columns the data type with numerical on the text changes. Prompt, how it is possible to save initial formatting ?

2

Re: Saving of initial formatting of cells at import of the table from Excel in Access

";" from a bracket underside.

3

Re: Saving of initial formatting of cells at import of the table from Excel in Access

kudim wrote:

columns the data type with numerical on the text changes.

Means in these columns in  text data type or # in the first 20 lines

4

Re: Saving of initial formatting of cells at import of the table from Excel in Access

THE PENSIONER;
You are right, rechecked, and I in several connected documents have blank cells
In a column in which further in itself formatting changes.
After filling of these cells in zero, the problem like as disappeared. However, whether there is no method
To save initial formatting despite confusion in columns ? That is that it was not necessary
Each time to recheck an ex-fur-tree on presence of errors?

5

Re: Saving of initial formatting of cells at import of the table from Excel in Access

Whether

kudim wrote:

there is no method to save initial formatting despite confusion in columns ? That is that it was not necessary to recheck each time an ex-fur-tree on presence of errors?

If cells with numbers or empty in request it is possible to transform field type explicitly.
For example instead of a field "CCD_02_01" to specify
CDbl (NZ ([CCD_02_01]), 0)) As [CCD_02_01]
Or
CDbl (IIF (IsNull ([CCD_02_01]), 0, [CCD_02_01])) As [CCD_02_01]
Instead of CDbl it is possible to apply CCur, CLng, etc.

6

Re: Saving of initial formatting of cells at import of the table from Excel in Access

kudim;
a piece from  and a piece with the table of a database and  here on a forum, then it is possible to think and the real decision to invent not to guess why it is impossible to you. Sometimes banal things happen: it is necessary to export the table an ex-fur-tree in csv a format, and then import in  can work.

7

Re: Saving of initial formatting of cells at import of the table from Excel in Access

The pathos farmer;
I spread to you a problem line in a format . In it as wrote in the previous messages, there are blank lines in the beginning (numbers begin only from 454 lines). Therefore the cell number format in   is perceived as the text.
It is clear that most easier to fill their zero, however it is inconvenient to do each time.
The code offered by Anatoly

CDbl (NZ ([CCD_02_01]), 0)) As [CCD_02_01]

really helps to save initial formatting of numerical cells, however works only on one computer. On other for some reason there is an error "an alias
CCD_02_01 (This title of a column) led to circular reference origin ".
So now I try to understand what exactly this code line does and for what reason there is an error. I will be glad to your help)

8

Re: Saving of initial formatting of cells at import of the table from Excel in Access

kudim wrote:

CCD_02_01 (this title of a column) led to circular reference origin

Add a table name before a name of a column and the error disappears. Well or rename a calculated column.

9

Re: Saving of initial formatting of cells at import of the table from Excel in Access

And is better import the data at first to the existing temporary table with all fields of type the Text and names of columns, for example F1, F2... F100, and then request already from this table write where it is necessary with all necessary conversion. So  it is more reliable. If there will be the "bad" data (and it is necessary to calculate for it!) which cannot be converted, can produce correct diagnostics to the user instead of a muffled error or the silent pass of the erratic data.

10

Re: Saving of initial formatting of cells at import of the table from Excel in Access

MrShin;
At me  files are anchored to , that is there only one page (as on a screenshot) I Try to write down as follows, but produces syntax errors.

CDbl (IIF (IsNull ([(SELECT [net weight ()] FROM [ 2015])), 0, (SELECT [net weight ()] FROM [ 2015]) As (SELECT [net weight ()] FROM [ 2015])

11

Re: Saving of initial formatting of cells at import of the table from Excel in Access

MrShin;
Screenshot