question

IvanTorr-4342 avatar image
0 Votes"
IvanTorr-4342 asked KenSheridan-7466 answered

Null value in access query

I have a query that runs on a form QryRecProducts

SELECT TblProdList.ProdID, TblProdList.ProdName, TblProdList.ProdActive, TblSupp.SuppActive
FROM TblProdList INNER JOIN TblSupp ON TblProdList.SuppID = TblSupp.SuppID
WHERE (((TblProdList.ProdName) Like "*") AND ((TblProdList.ProdActive)=Yes) AND ((TblSupp.SuppActive)=Yes))
ORDER BY TblProdList.ProdName;

and

SELECT QryRecProducts.ProdQty, QryRecProducts.RecID, QryRecProducts.ProductID, QryRecProducts.ProdName, QryRecProducts.ProdPrice, QryRecProducts.ProdSize1, QryRecProducts.Cost, QryRecProducts.Kcal, QryRecProducts.ProdFood
FROM QryRecProducts
WHERE (((QryRecProducts.ProdName) Like "*";


On the form when i enter a value in the ProdId field it rums well and the form works as it should,
if I need to go back and delete it the query will not let me exit the field, i get an error "you tried to assign a Null value to a variable that is not a Variant data type"

I have tried a few ideas like "Or Is Null" but nothing works

any Ideas

Regards

Ivan

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

From its name my guess would be that the ProdID column is the table's primary key, or at least a candidate key whose Required property is True. A primary key cannot be NULL, it must contain a distinct value of the attribute represented by the column. NULL is not a value, but the absence of a value.

However, why would you ever wasn’t to delete a key's value? You might wish to amend it to another value, but, as it is the key which uniquely identifies each row in a table there can never be a circumstance in which it would be deleted. If you wish to amend it simply overwrite the current value. Note BTW that if a primary key can be edited Cascade Updates should be enforced it any relationship the table has with a referencing table.

I would also question the use of the LIKE operator with the * wildcard character. As I mentioned in another thread, this will merely restrict the result set to those rows where the column position in question is not Null, because, as the absence of a value, NULL cannot be LIKE anything, even another NULL. To restrict a query in this way IS NOT NULL should be used.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.