Excel Access Transfer

[rchilds]

I have an excel spreadsheet I have been transferring into an access database
for a while. I had to make a change recently so that it would leave blank
cells as blanks instead of putting in zero's. Now when I run my macro to pull
from excel to access I get a type conversion on all cells that have blanks. I
am getting this regardless of whether text, number, decimal or memo type cell
in access.

PLEASE HELP.

 

[KARL DEWEY]

Why not allow the zeros and then drop with an IIF statement in your query,
form, or report display?

 

[rchilds]

I did that in excel but now I get a type conversion failure error when
importing into access.

Why not allow the zeros and then drop with an IIF statement in your query,
form, or report display?

I have an excel spreadsheet I have been transferring into an access database
for a while. I had to make a change recently so that it would leave blank

[quoted text clipped - 4 lines]

PLEASE HELP.

 

[KARL DEWEY]

I assume you did this ---
IIF(xyz = 0, "", xyz) or some such. This puts a zero lenght STRING
instead of a number so that is why the 'type conversion failure error.'

You would not get an error using IIF(xyz = 0, Null, xyz) to replace the
zeros.
--
KARL DEWEY
Build a little - Test a little

I did that in excel but now I get a type conversion failure error when
importing into access.

Why not allow the zeros and then drop with an IIF statement in your query,
form, or report display?

I have an excel spreadsheet I have been transferring into an access database
for a while. I had to make a change recently so that it would leave blank

[quoted text clipped - 4 lines]

PLEASE HELP.

 

[rchilds via AccessMonster.com]

I did as you suggested but I am still getting the type conversion error plus
now while I don't get an error in text columns I am getting #Name in those
columns. I have checked everything to ensure that my excel cells are
formatted like my access ones are, that all fields allow for zero length.....

What am I doing wrong or is this a problem I will just have to learn to live
with?

I assume you did this ---
IIF(xyz = 0, "", xyz) or some such. This puts a zero lenght STRING
instead of a number so that is why the 'type conversion failure error.'

You would not get an error using IIF(xyz = 0, Null, xyz) to replace the
zeros.

I did that in excel but now I get a type conversion failure error when
importing into access.

[quoted text clipped - 6 lines]

 

[KARL DEWEY]

When you run your 'macro to pull from excel to access' is it appending to an
existing table or making a new one?

If you are appending what kind of constraints does the field have?
Required? Validation? etc.

--
KARL DEWEY
Build a little - Test a little

I did as you suggested but I am still getting the type conversion error plus
now while I don't get an error in text columns I am getting #Name in those
columns. I have checked everything to ensure that my excel cells are
formatted like my access ones are, that all fields allow for zero length.....

What am I doing wrong or is this a problem I will just have to learn to live
with?

I assume you did this ---
IIF(xyz = 0, "", xyz) or some such. This puts a zero lenght STRING
instead of a number so that is why the 'type conversion failure error.'

You would not get an error using IIF(xyz = 0, Null, xyz) to replace the
zeros.

I did that in excel but now I get a type conversion failure error when
importing into access.

[quoted text clipped - 6 lines]

PLEASE HELP.

 

[rchilds via AccessMonster.com]

It is appending an existing table, no validation setting for text boxes none
are listed as required. For numeric or decimal fields validation is some
version of <>0 Or Is Null Or =0. I only get the coversion type error for
numeric fields with blank values. For the text fields that are blank I get
#Name? instead of blank in the table.

When you run your 'macro to pull from excel to access' is it appending to an
existing table or making a new one?

If you are appending what kind of constraints does the field have?
Required? Validation? etc.

I did as you suggested but I am still getting the type conversion error plus
now while I don't get an error in text columns I am getting #Name in those

[quoted text clipped - 15 lines]