Is this a bug? Inexplicable "You tried to assign the Null value to a variable that is not a Variant data type." error.

[existential...@gmail.com]

I'm getting an error which I cannot explain. I'd be grateful if
someone else would look at it and help me figure out what is going on.

In a nutshell, I get the error "You tried to assign the Null value to
a variable that is not a Variant data type." when I change a value to
Null in query design view. The query is updatable and the Required
property for the changed column is set to "No", so changing the value
to Null should be allowed.

To see the problem for yourself, open the database referenced below,
then open the query ArgumentRW, delete ElementID 656, then hit Return.
You should get the error I mentioned. (If you don't, please let me
know.)

ftp://share.matsonconsulting.com/AssignNullError.mdb

Maybe I'm missing something obvious, but my best guess is that this is
a bug, and that under some circumstances, "updatable" queries don't
support the update operations they should support. If that guess is
correct, it would be very bad for me; this error is occurring in an
application which was designed to use updatable queries as the source
for all forms. If updatable queries aren't reliable, I'll have to do a
major redesign of the application to base my forms on tables instead.

I'm using Access 2003 SP3.

-TC

[Tony Toews [MVP]]

existential...@gmail.com wrote:

>In a nutshell, I get the error "You tried to assign the Null value to
>a variable that is not a Variant data type." when I change a value to
>Null in query design view. The query is updatable and the Required
>property for the changed column is set to "No", so changing the value
>to Null should be allowed.

I've never received that message that I can recall. I'm not quite sure why.
Although I generally use right joins in queries and not equal joins.

>To see the problem for yourself, open the database referenced below,
>then open the query ArgumentRW, delete ElementID 656, then hit Return.
>You should get the error I mentioned. (If you don't, please let me
>know.)

Yes, I did get the error message.

>Maybe I'm missing something obvious, but my best guess is that this is
>a bug, and that under some circumstances, "updatable" queries don't
>support the update operations they should support. If that guess is
>correct, it would be very bad for me; this error is occurring in an
>application which was designed to use updatable queries as the source
>for all forms. If updatable queries aren't reliable, I'll have to do a
>major redesign of the application to base my forms on tables instead.

I don't have an explanation. However changing the queries Recordset Type to Dynaset
(Inconsistent Updates) or using a right join fixed this problem.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[Salad]

existential...@gmail.com wrote:

I don't know how many people attempt to "delete" the linked field data
in a query. Your current status is an inner join. If I also had 657 in
both tables then changed it to 656, it'd be ok because 656 exists.
Access doesn't seem to want assign a null to the link. I tried to
change it to 777 and it complained about that since there was no
matching id 777 record in Element.

This seems to work. Kinda dumb tho, breaking the link between the two
tables...but there must be a reason for you to do so.

SELECT Argument.ArgumentID, Argument.ElementID
FROM Argument LEFT JOIN Element ON Argument.ElementID = Element.ElementID;

[David W. Fenton]

Salad <o...@vinegar.com> wrote in
news:7_qdnYUSHq9rp2zU...@earthlink.com:

> This seems to work. Kinda dumb tho, breaking the link between the
> two tables...but there must be a reason for you to do so.
>
> SELECT Argument.ArgumentID, Argument.ElementID
> FROM Argument LEFT JOIN Element ON Argument.ElementID =
> Element.ElementID;

The Jet SQL-specific DISTINCTROW predicate also fixes the issue:

SELECT DISTINCTROW Argument.*
FROM Element INNER JOIN Argument ON Element.ElementID =
Argument.ElementID;

I've used this kind of thing when editing data for import and
normalization, where you want to edit join data by, for instance,
replacing two identical parents with a single parent.

Frankly, an example like this that is boiled down to be completely
abstract is very difficult for me to evaluate. If there were
real-world entities and attributes it would be a lot easier to
figure out what is needed and how to accomplish it.

I am also weirded out by the error message, which uses the term
"Variant" which, so far as I know, is a VBA term and not a Jet term.
There's no such thing as a variant data type in a data table, so
that message seems particularly strange to me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/