Null value in access query

Ivan Torr 1 Reputation point
2021-09-24T22:47:13.373+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
856 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Ken Sheridan 2,756 Reputation points
    2021-09-26T00:10:01.323+00:00

    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.

    0 comments No comments