How to Enter Blank into Access

[biocentrism]

I am trying to write a program which enters the value of a BLANK Excel cell into Access as a BLANK.

But what I keep getting is a zero.



Cell A1 is a blank. What am I doing wrong?

Dim mydata as Double

mydata= Range("A1").value

SQL = "INSERT INTO MyTable ([Data]) VALUES ('" & mydata & "')"

I have set Access to accept zero-length data as well.

Thank you.

[CJ_London]

an excel cell can be any datatype - text, number, anything.

an access field has a specified datatype - text or number or something else

so my guess is your field called data has been specified as a number.

the word 'Blank' is text so cannot be used to populate a number field, however you can set formats so that the word 'blank' appears if the number is zero - in the format property for the field in table design put

;;"BLANK"

if you want the text 'BLANK' then set the datatype to text - but then you cannot add/subtract/multiply etc the non blank values without using additional functions to convert text to numbers (like the value function in excel)

Note that format properties are not passed on when querying etc, so most people just set the format property where it matters - in a form or report.

[biocentrism]

Thank you. I wasn't clear in my explanation. I meant blank as in empty cell. Not the text blank. How do I get the code to insert an empty cell into access?

[CJ_London]

not sure you can in this way because you need to import a null value - excel can be a problem. And anyway you are trying to import text (so try removing the single quotes) but my guess is it will then fail because you are trying to assign a null value and so far as I am aware, this will cause the query to fail and excel does not recognise null as a form of value.

to import a null value your sql would need to look something like

INSERT INTO myTable (Data) Values ()

which will fail and I don't think you can build it in Excel anyway

suggest explain what you are really trying to do