Excel transfer to access

[Guest]

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 cell type.

PLEASE HELP.

 

[Guest]

Dear "Dallasbutterfly"

Check the definition of the fields where your transferring the blank data
cells into. I would guess they were defined numeric. If you were not the
designer/programmer that created the access table where the data is being
transferred into, then I would change the Excel spreadsheet back to the way
it use to be. Those zeroes may have been serving a purpose in the Access side
of things, find the designer/programmer to resolve it. You could have a
access report that would blow up if it gets blanks instead of numbers.

Good Luck,
Steve

 

[Guest]

We are taking data from a form in excel, storing it in access and as needed
transferring it back out to excel for graphing. The end user does need the
blanks from the original form to remain blank. I did write the programs that
1. took the data from original form, turned it into form for importing into
access and then as needed exporting back out. We receive the data forms daily
but needed them combined into 1 program. I have checked the individual cells
in both the excel spreadsheet and in access to make sure they are same types
and that in access the cells are set to allow zero length or null value.
However when I import the data using a transfer spreadsheet macro I get a
type conversion failure note in a seperate table in access. The actual data
in access shows the blanks where they are supposed to be. What I want to
eliminate is the error message as I get a seperate error table with each
import. I have done everything both access help and excel help told me to do
but can't find any information on the error message. The only change I made
to the excel program was to tell the program converting the data into a
readable format for access to allow blanks to stay blank by using an if
statement. The conversions between programs were working perfectly except
that the conversion program was putting in 0's in place of blanks.

Is there a way to convert withour getting the type conversion failure error
message?
-Robyn

 

[George Nicholson]

The only change I made

to the excel program was to tell the program converting the data into a
readable format for access to allow blanks to stay blank by using an if
statement.

What *exactly* did you tell it to do?

Did you write something like "if x is blank use "" otherwise use x?"

If so, change it to "if x is blank, use Null, otherwise use x"

A Type Conversion error would easily result from trying to force a string
("") into a numeric field.

HTH,

 

[Guest]

Dear "Dallasbutterfly"

Another question, are you using a import specification for the import? If
yes, maybe you need to modify the import sepcification to accept blanks,
instead of expecting numeric?

Good Luck,
Steve