I'm getting an error that I don't know how to fix. I'm using odbc to connect to a mysql database. When I bring the table up in Access and try to delete a value, it gives me this error:

"You tried to assign the Null value to a variable that is not a Variant data type."

I'm new at this, so I'm not sure what setting or field setting within the database table that I need to modify to allow me to delete data from individual cells within the database table.

Can anyone help?

El 24/03/2011 16:12, chadlupkes escribió/wrote:
You give very little detail (and I can't figure out what Access has to
do with all this) but you can't assign a NULL value to a column that's
NOT NULL, e.g.:

CREATE TABLE `test` (
`foo` VARCHAR(50) NOT NULL,
`bar` VARCHAR(50)
);

These are okay:

INSERT INTO TEST (foo, bar) VALUES ('Hello', 'World');
INSERT INTO TEST (foo, bar) VALUES ('Hello', NULL);

This is not:

INSERT INTO TEST (foo, bar) VALUES (NULL, 'World');

SQL Error (1048): Column 'foo' cannot be null


--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--

On 3/24/2011 11:12 AM, chadlupkes wrote:
Evidently Access is trying to assign a NULL value to the field, but the
field is set up to not allow NULL values.

For instance, a employee table would have (among other things), first
name and last name. You would never have an employee without both, so
the table should never contain NULL values for these columns. The
employee might, however, not have a cell phone number (unlikely today,
but this is an example). This column would then be able to contain a
NULL value, indicating no cell phone number.

Note that NULL is NOT a value. It is specifically the ABSENCE of a
value, i.e. "don't know", "don't care" , "not applicable", etc. For
numeric values, this would be different from 0, 42, -196 or any other
value. For strings, this is different than the characters 'NULL' or an
empty string (a string with zero characters, i.e. ''). Both are values.
The same idea goes for other non-numeric fields such as blobs.

So why is your database set up to not accept NULL values in this column?
Obviously whomever designed the database determined the column in
question required some data, and you won't be able to just delete the
data; you'll have to have SOME value in there.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

Jerry got it right. So, if a MySQL field has a Null value in the design, it means it allows Null values. Counter intuitive, but ok. It didn't work when I changed the setting to either Null or "Not Null". So something else must be getting in the way.